SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Get list of all databases, tables and columns???
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wgpubs
Yak Posting Veteran

67 Posts

Posted - 04/26/2004 :  13:05:09  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 04/26/2004 :  13:06:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/26/2004 :  13:09:09  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 04/26/2004 :  13:17:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 04/26/2004 13:18:12
Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 04/26/2004 :  13:28:12  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 04/26/2004 :  13:30:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
SELECT name
INTO #Temp
FROM master.dbo.sysdatabases

Tara
Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 04/26/2004 :  13:37:17  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 04/26/2004 :  13:40:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Have you looked into dynamic sql?

Tara
Go to Top of Page

wgpubs
Yak Posting Veteran

67 Posts

Posted - 04/26/2004 :  13:51:07  Show Profile  Reply with Quote
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

USA
36582 Posts

Posted - 04/26/2004 :  14:06:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/26/2004 :  15:50:42  Show Profile  Reply with Quote
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 - 04/27/2004 :  18:25:18  Show Profile  Reply with Quote
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 - 04/28/2004 :  14:31:48  Show Profile  Reply with Quote
Why not use sp_tables and sp_columns?

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36582 Posts

Posted - 04/28/2004 :  14:38:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000