Inventory a Server

[John Whitehead]

I'd like to write a query that will inventory a server. Specifically, I'd
like to be able to know the tables associated with each database, along with
pertinent table information (size, indexes, contraints, etc.).

I realize that I can get most of the information about tables from the
sysobjects table for each database- but I don't know of a way to link tables
back to individual databases (I think this info resides in the master
database).

I'd appreciate any suggestions or ideas...Thanks in advance!

jw

[Michael Levy]

John,

Take a hard look at SQL-DMO. It already performs most of those tasks.
There's a database collection, which has a table collection, (with a columns
collection), a collection of stored procedures, ... probably everything that
you're looking for.

-Mike
--
Michael Levy MCSD, MCDBA, MCT
Consultant
GA Sullivan
michaell@gasullivan.com

[Ron Niewohner]

"John Whitehead" <jwhite@psecu.com> wrote:
>I'd like to write a query that will inventory a server. Specifically, I'd
>like to be able to know the tables associated with each database, along
with
>pertinent table information (size, indexes, contraints, etc.).
>
>I realize that I can get most of the information about tables from the
>sysobjects table for each database- but I don't know of a way to link tables
>back to individual databases (I think this info resides in the master
>database).
>
>I'd appreciate any suggestions or ideas...Thanks in advance!
>
>jw
>
>
John,
Take a look at sp_spaceused. The tables you will need are sysobjects, sysindexes
and sysconstraints. I have a stored procedure writen that gets all the tables
and indexs space information. If you want it let me know (I'll need to find
it) and I'll send it to you.

[John Whitehead]

Thanks Mike, I looked over the DMO info in BOL... Eventually, I'm going to
try and experiment with DMO...Is it possible to install only the DMO support
files onto a client machine?

In the interim, I still need to know if there is a programatic way to cycle
through databases... I tried the SET command with a variable (below) but I
keep getting an error:

DECLARE @next VARCHAR(30)
DECLARE @db varchar(30)
SELECT @next = ' ' --Makes it "not null"
SELECT @db=' ' --Not Null
WHILE @db IS NOT NULL
BEGIN
SELECT @db = Min(name) from master..sysdatabases where
master..sysdatabases.name > @db
PRINT "==" + @db + "=="
USE @db --<---------Here's where it bombs
WHILE @next IS NOT NULL
BEGIN
SELECT @next = MIN(name) FROM sysobjects
WHERE type = 'U' AND name > @next
EXECUTE sp_spaceused @next
END
SELECT @next = ' ' --Makes it "not null"
PRINT "++++++++++++++++++++++++++++++++"
END

ERROR: Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '@db'.

Thanks again for your help.

jw
Michael Levy wrote in message <39591e43@news.devx.com>...
>John,
>
>Take a hard look at SQL-DMO. It already performs most of those tasks.
>There's a database collection, which has a table collection, (with a
columns
>collection), a collection of stored procedures, ... probably everything
that
>you're looking for.
>
>-Mike
>--
>Michael Levy MCSD, MCDBA, MCT
>Consultant
>GA Sullivan
>michaell@gasullivan.com
>
>
>

[John Whitehead]

Ron,

Anything you have would be appreciated. The key issue I'm hanging on is a
way to programatically (via stored procedures) cycle through all of the
databases on a server.

I tried this code and got an error:

DECLARE @next VARCHAR(30)
DECLARE @db varchar(30)
SELECT @next = ' ' --Makes it "not null"
SELECT @db=' ' --Not Null
WHILE @db IS NOT NULL
BEGIN
SELECT @db = Min(name) from master..sysdatabases where
master..sysdatabases.name > @db
PRINT "==" + @db + "=="
USE @db --<---------Here's where it bombs
WHILE @next IS NOT NULL
BEGIN
SELECT @next = MIN(name) FROM sysobjects
WHERE type = 'U' AND name > @next
EXECUTE sp_spaceused @next
END
SELECT @next = ' ' --Makes it "not null"
PRINT "++++++++++++++++++++++++++++++++"
END

ERROR: Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '@db'.

I'm not sure why I can't use a variable with the SET statement. I even
tried concatonating the database name into the select statement (ie:
databasename..sysobjects) but I got the same thing.

Appreciate your help.

jw


Ron Niewohner wrote in message <395a2b7b$1@news.devx.com>...
>
>"John Whitehead" <jwhite@psecu.com> wrote:
>>I'd like to write a query that will inventory a server. Specifically, I'd
>>like to be able to know the tables associated with each database, along
>with
>>pertinent table information (size, indexes, contraints, etc.).
>>
>>I realize that I can get most of the information about tables from the
>>sysobjects table for each database- but I don't know of a way to link
tables
>>back to individual databases (I think this info resides in the master
>>database).
>>
>>I'd appreciate any suggestions or ideas...Thanks in advance!
>>
>>jw
>>
>>
>John,
>Take a look at sp_spaceused. The tables you will need are sysobjects,
sysindexes
>and sysconstraints. I have a stored procedure writen that gets all the
tables
>and indexs space information. If you want it let me know (I'll need to find
>it) and I'll send it to you.
>
>

[John Whitehead]

To clarify, Its the USE statement I'm running into trouble with not the SET
statement.

jw
John Whitehead wrote in message <395b9ad5@news.devx.com>...
>Ron,
>
>Anything you have would be appreciated. The key issue I'm hanging on is a
>way to programatically (via stored procedures) cycle through all of the
>databases on a server.
>
>I tried this code and got an error:
>
>DECLARE @next VARCHAR(30)
>DECLARE @db varchar(30)
>SELECT @next = ' ' --Makes it "not null"
>SELECT @db=' ' --Not Null
>WHILE @db IS NOT NULL
>BEGIN
>SELECT @db = Min(name) from master..sysdatabases where
>master..sysdatabases.name > @db
>PRINT "==" + @db + "=="
>USE @db --<---------Here's where it bombs
>WHILE @next IS NOT NULL
>BEGIN
> SELECT @next = MIN(name) FROM sysobjects
> WHERE type = 'U' AND name > @next
> EXECUTE sp_spaceused @next
>END
>SELECT @next = ' ' --Makes it "not null"
>PRINT "++++++++++++++++++++++++++++++++"
>END
>
>ERROR: Server: Msg 170, Level 15, State 1, Line 9
>Line 9: Incorrect syntax near '@db'.
>
>I'm not sure why I can't use a variable with the SET statement. I even
>tried concatonating the database name into the select statement (ie:
>databasename..sysobjects) but I got the same thing.
>
>Appreciate your help.
>
>jw
>
>
>Ron Niewohner wrote in message <395a2b7b$1@news.devx.com>...
>>
>>"John Whitehead" <jwhite@psecu.com> wrote:
>>>I'd like to write a query that will inventory a server. Specifically,
I'd
>>>like to be able to know the tables associated with each database, along
>>with
>>>pertinent table information (size, indexes, contraints, etc.).
>>>
>>>I realize that I can get most of the information about tables from the
>>>sysobjects table for each database- but I don't know of a way to link
>tables
>>>back to individual databases (I think this info resides in the master
>>>database).
>>>
>>>I'd appreciate any suggestions or ideas...Thanks in advance!
>>>
>>>jw
>>>
>>>
>>John,
>>Take a look at sp_spaceused. The tables you will need are sysobjects,
>sysindexes
>>and sysconstraints. I have a stored procedure writen that gets all the
>tables
>>and indexs space information. If you want it let me know (I'll need to
find
>>it) and I'll send it to you.
>>
>>
>
>

[John Whitehead]

To clarify, Its the USE statement I'm running into trouble with not the SET
statement.

jw

John Whitehead wrote in message <395b984f$1@news.devx.com>...
>Thanks Mike, I looked over the DMO info in BOL... Eventually, I'm going to
>try and experiment with DMO...Is it possible to install only the DMO
support
>files onto a client machine?
>
>In the interim, I still need to know if there is a programatic way to cycle
>through databases... I tried the SET command with a variable (below) but I
>keep getting an error:
>
>DECLARE @next VARCHAR(30)
>DECLARE @db varchar(30)
>SELECT @next = ' ' --Makes it "not null"
>SELECT @db=' ' --Not Null
>WHILE @db IS NOT NULL
>BEGIN
>SELECT @db = Min(name) from master..sysdatabases where
>master..sysdatabases.name > @db
> PRINT "==" + @db + "=="
> USE @db --<---------Here's where it bombs
> WHILE @next IS NOT NULL
> BEGIN
> SELECT @next = MIN(name) FROM sysobjects
> WHERE type = 'U' AND name > @next
> EXECUTE sp_spaceused @next
> END
> SELECT @next = ' ' --Makes it "not null"
> PRINT "++++++++++++++++++++++++++++++++"
>END
>
>ERROR: Server: Msg 170, Level 15, State 1, Line 9
>Line 9: Incorrect syntax near '@db'.
>
>Thanks again for your help.
>
>jw
>Michael Levy wrote in message <39591e43@news.devx.com>...
>>John,
>>
>>Take a hard look at SQL-DMO. It already performs most of those tasks.
>>There's a database collection, which has a table collection, (with a
>columns
>>collection), a collection of stored procedures, ... probably everything
>that
>>you're looking for.
>>
>>-Mike
>>--
>>Michael Levy MCSD, MCDBA, MCT
>>Consultant
>>GA Sullivan
>>michaell@gasullivan.com
>>
>>
>>
>
>

[Jason Langston]

John,
For a plain SQL statement that you build from parameters, you first build
the SQL, then EXEC it ie:
@SQL = "SELECT " + @ColumnParam + " FROM " + @TableParam + " WHERE "
@WhereParam
EXEC (@SQL)
John Whitehead <jwhite@psecu.com> wrote> I tried this code and got an error:
> <snip>
> USE @db --<---------Here's where it bombs
> WHILE @next IS NOT NULL
> BEGIN
> SELECT @next = MIN(name) FROM sysobjects
> WHERE type = 'U' AND name > @next
> EXECUTE sp_spaceused @next
> END
> SELECT @next = ' ' --Makes it "not null"
> PRINT "++++++++++++++++++++++++++++++++"
> END
You'll have to play with splitting up the SQL from the BEGIN .. and EXEC
statements,
HTH,
-JasonL

[Michael Levy]

OK, it appears that you cannot supply a parameter to the USE statement. So,
since sp_spaceused must exist within the context of the target database, try
combining the USE and the call to sp_spaceused in the same dynamic sql
batch. You'll have to move the USE into the loop

EXECUTE ('USE ' + @db + '; EXECUTE sp_spaceused ' + @next)

-Mike
--
Michael Levy MCSD, MCDBA, MCT
Consultant
GA Sullivan
michaell@gasullivan.com