Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Get list of all databases, tables and columns???

Author  Topic 

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-04-26 : 13:05:09
Hi there,

Looking for the best way to programmatically generate a list of all the databases on a particular SQL Server box ... and for each database, include a list of tables ... and for each table ... include a list of columns with column info.

Kinda like ...

Database 1
Table 1
ColumnName 1 varchar (500)
ColumnName 2 int
Table 2
....
etc...

Any ideas would be appreciated! Thanks much - wg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 13:06:41
SELECT * FROM master.dbo.sysdatabases

USE DB1
GO

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

USE DB2
GO

...

Tara
Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-04-26 : 13:09:09
Is there a way to group this information? so i would get something returned like this ...

Database 1
---- Table 1
----------- Colnmame
----------- Colnanem
---- TAble 2
...
Database 2 ... etc...

thanks - wg

quote:
Originally posted by tduggan

SELECT * FROM master.dbo.sysdatabases

USE DB1
GO

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

USE DB2
GO

...

Tara

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 13:17:28
You could do it with dynamic sql. You wouldn't be able to use USE DBName as each executed string operates independently.

So you would put the database names into a temp table. Then put them one at a time into a variable and run this each time:

SELECT * FROM @DBName.INFORMATION_SCHEMA.TABLES
...

You would have to do it dynamically as the query won't compile as is.


Tara
Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-04-26 : 13:28:12
ok ... but is there a way to programmatically pull all the databases on the server into the temp table?

quote:
Originally posted by tduggan

You could do it with dynamic sql. You wouldn't be able to use USE DBName as each executed string operates independently.

So you would put the database names into a temp table. Then put them one at a time into a variable and run this each time:

SELECT * FROM @DBName.INFORMATION_SCHEMA.TABLES
...

You would have to do it dynamically as the query won't compile as is.


Tara

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 13:30:27
SELECT name
INTO #Temp
FROM master.dbo.sysdatabases

Tara
Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-04-26 : 13:37:17
OK thanks for your help ... I'm getting there :)

How about rolling up the information? Ideally I'd like to generate something I can just cut & past into excel that shows everything grouped correctly (basically how I mentioned it in my previous postings) ... so for every db, there would be a list of tables ... and for ea. table, a list of columns.

Not sure at all how to do this (but i know its possible). Any ideas on how to formulate it?

Thanks again - wg

quote:
Originally posted by tduggan

SELECT name
INTO #Temp
FROM master.dbo.sysdatabases

Tara

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 13:40:55
Have you looked into dynamic sql?

Tara
Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-04-26 : 13:51:07
Yes, ... I just thought there was a more elegant way to do this via groupby, rollup, etc...

quote:
Originally posted by tduggan

Have you looked into dynamic sql?

Tara

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-26 : 14:06:08
No there isn't. Here's a start for you though. It doesn't put it in the format that you want, but it gives you a start. I just don't have the time to finish it:



SET NOCOUNT ON

DECLARE @RowCnt INT
DECLARE @WhichDB SYSNAME
DECLARE @SQL VARCHAR(7000)

CREATE TABLE #Temp
(
DBID INT IDENTITY(1, 1) NOT NULL,
DBName SYSNAME NOT NULL
)

INSERT INTO #Temp (DBName)
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model', 'pubs', 'NorthWind')

SELECT @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

SELECT TOP 1 @WhichDB = DBName
FROM #Temp

SELECT @WhichDB

EXEC ('SELECT TABLE_NAME FROM ' + @WhichDB + '.INFORMATION_SCHEMA.TABLES')
EXEC ('SELECT TABLE_NAME, COLUMN_NAME FROM ' + @WhichDB + '.INFORMATION_SCHEMA.COLUMNS ORDER BY ORDINAL_POSITION')

DELETE FROM #Temp
WHERE DBName = @WhichDB

SELECT @RowCnt = @@ROWCOUNT

END

SELECT *
FROM #Temp

DROP TABLE #Temp




It is very slow as it has to do dynamic sql and a loop. I hope you don't plan on adding it to an application. It should be used for informational purposes only for a DBA or developer.

Tara
Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-04-26 : 15:50:42
cool ... thanks for your help!

quote:
Originally posted by tduggan

No there isn't. Here's a start for you though. It doesn't put it in the format that you want, but it gives you a start. I just don't have the time to finish it:



SET NOCOUNT ON

DECLARE @RowCnt INT
DECLARE @WhichDB SYSNAME
DECLARE @SQL VARCHAR(7000)

CREATE TABLE #Temp
(
DBID INT IDENTITY(1, 1) NOT NULL,
DBName SYSNAME NOT NULL
)

INSERT INTO #Temp (DBName)
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model', 'pubs', 'NorthWind')

SELECT @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

SELECT TOP 1 @WhichDB = DBName
FROM #Temp

SELECT @WhichDB

EXEC ('SELECT TABLE_NAME FROM ' + @WhichDB + '.INFORMATION_SCHEMA.TABLES')
EXEC ('SELECT TABLE_NAME, COLUMN_NAME FROM ' + @WhichDB + '.INFORMATION_SCHEMA.COLUMNS ORDER BY ORDINAL_POSITION')

DELETE FROM #Temp
WHERE DBName = @WhichDB

SELECT @RowCnt = @@ROWCOUNT

END

SELECT *
FROM #Temp

DROP TABLE #Temp




It is very slow as it has to do dynamic sql and a loop. I hope you don't plan on adding it to an application. It should be used for informational purposes only for a DBA or developer.

Tara

Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2004-04-27 : 18:25:18
Also, what about items like DTS packages, views, etc...? What type of query would I need to formulate to pull that info?
Go to Top of Page

afterburn
Starting Member

28 Posts

Posted - 2004-04-28 : 14:31:48
Why not use sp_tables and sp_columns?

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-28 : 14:38:01
You should not use system tables if the information is available in the INFORMATION_SCHEMA views.

wgpubs, look at all of the INFORMATION SCHEMA views to see if they can give you what you need. They can be found in the master database.

Tara
Go to Top of Page
   

- Advertisement -