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.sysdatabasesUSE DB1GOSELECT * FROM INFORMATION_SCHEMA.TABLESSELECT * FROM INFORMATION_SCHEMA.COLUMNSUSE DB2GO...Tara |
|
|
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 - wgquote: Originally posted by tduggan SELECT * FROM master.dbo.sysdatabasesUSE DB1GOSELECT * FROM INFORMATION_SCHEMA.TABLESSELECT * FROM INFORMATION_SCHEMA.COLUMNSUSE DB2GO...Tara
|
|
|
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 |
|
|
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
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-26 : 13:30:27
|
SELECT nameINTO #TempFROM master.dbo.sysdatabasesTara |
|
|
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 - wgquote: Originally posted by tduggan SELECT nameINTO #TempFROM master.dbo.sysdatabasesTara
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-26 : 13:40:55
|
Have you looked into dynamic sql?Tara |
|
|
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
|
|
|
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 ONDECLARE @RowCnt INTDECLARE @WhichDB SYSNAMEDECLARE @SQL VARCHAR(7000)CREATE TABLE #Temp(DBID INT IDENTITY(1, 1) NOT NULL,DBName SYSNAME NOT NULL)INSERT INTO #Temp (DBName)SELECT nameFROM master.dbo.sysdatabasesWHERE name NOT IN ('master', 'msdb', 'tempdb', 'model', 'pubs', 'NorthWind')SELECT @RowCnt = @@ROWCOUNTWHILE @RowCnt <> 0BEGIN 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 = @@ROWCOUNTENDSELECT *FROM #TempDROP 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 |
|
|
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 ONDECLARE @RowCnt INTDECLARE @WhichDB SYSNAMEDECLARE @SQL VARCHAR(7000)CREATE TABLE #Temp(DBID INT IDENTITY(1, 1) NOT NULL,DBName SYSNAME NOT NULL)INSERT INTO #Temp (DBName)SELECT nameFROM master.dbo.sysdatabasesWHERE name NOT IN ('master', 'msdb', 'tempdb', 'model', 'pubs', 'NorthWind')SELECT @RowCnt = @@ROWCOUNTWHILE @RowCnt <> 0BEGIN 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 = @@ROWCOUNTENDSELECT *FROM #TempDROP 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
|
|
|
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? |
|
|
afterburn
Starting Member
28 Posts |
Posted - 2004-04-28 : 14:31:48
|
Why not use sp_tables and sp_columns? |
|
|
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 |
|
|
|