Reputation: 11019
I would like to get a collection of all user databases, all the tables within each database, and columns and their datatypes for each of the tables. I have a SQL script that gets the database names I am looking for (and excludes those I am not, like system databases)...
DECLARE @DatabaseIgnoreList AS VARCHAR(500);
SET @DatabaseIgnoreList = 'DatabaseNotToInclude';
SELECT name, database_id
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1
AND name NOT IN ('msdb')
AND database_id > 4
AND name NOT LIKE '%$%'
AND name NOT IN (@DatabaseIgnoreList)
ORDER BY name ASC
I also have a separate SQL script that gets all the tables and columns from a specific database. I found this from this SO question/answer.
USE DatabaseName;
SELECT TABLE_SCHEMA ,
TABLE_NAME ,
COLUMN_NAME ,
ORDINAL_POSITION ,
COLUMN_DEFAULT ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
NUMERIC_PRECISION ,
NUMERIC_PRECISION_RADIX ,
NUMERIC_SCALE ,
DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS;
What I am not sure how to do is to link the two. For each database I would like to run the table/column discover script and end up with one single table that has the database name, table name, column name, and column datatype.
Is this a situation for a cursor? I am using SQL Server 2008+.
Upvotes: 1
Views: 939
Reputation: 33581
Here is another approach. This is not using any cursors or loops. It is a single result set with one extra column for the database name. There are several comments in the code to help.
FWIW - I just tested this on a dev server that has 60 databases. It returned all 165,000+ rows in just over 15 seconds.
DECLARE @DatabaseIgnoreList AS VARCHAR(500);
SET @DatabaseIgnoreList = 'DatabaseNotToInclude';
declare @SQL nvarchar(max) = ''
select @SQL = @SQL +
'SELECT ''' + quotename(name) + ''' as DatabaseName,
TABLE_SCHEMA = TABLE_SCHEMA collate database_default,
TABLE_NAME = TABLE_NAME COLLATE database_default ,
COLUMN_NAME = COLUMN_NAME COLLATE database_default ,
ORDINAL_POSITION ,
COLUMN_DEFAULT = COLUMN_DEFAULT COLLATE database_default ,
DATA_TYPE = DATA_TYPE COLLATE database_default ,
CHARACTER_MAXIMUM_LENGTH ,
NUMERIC_PRECISION ,
NUMERIC_PRECISION_RADIX ,
NUMERIC_SCALE ,
DATETIME_PRECISION
FROM ' + quotename(name) + '.INFORMATION_SCHEMA.COLUMNS union all '
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1
--AND name NOT IN ('msdb') --why bother? You already exclude this with database_id > 4
AND database_id > 4
AND name NOT LIKE '%$%'
AND name <> @DatabaseIgnoreList --if you need to exclude more than 1 we need a little different approach here
and state_desc = 'ONLINE' --want to exclude any offline databases
ORDER BY name ASC
set @SQL = left(@SQL, LEN(@SQL) - 10) + ' order by DatabaseName, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME' --This will order the entire result set
select @SQL
--uncomment the line below when you are satisfied the dynamic sql is correct
--exec sp_executesql @SQL
Upvotes: 2
Reputation: 43
This is not the best way but it works
DECLARE @DatabaseIgnoreList AS VARCHAR(500);
SET @DatabaseIgnoreList = 'DatabaseNotToInclude';
SELECT name, database_id
into #temporaltable
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1
AND name NOT IN ('msdb')
AND database_id > 4
AND name NOT LIKE '%$%'
AND name NOT IN (@DatabaseIgnoreList)
ORDER BY name ASC
declare
@target varchar(250),
@statement varchar(max)
declare @schemas as CURSOR;
set @schemas = CURSOR FOR
select name from #temporaltable;
open @schemas;
fetch next from @schemas INTO @target
while @@FETCH_STATUS = 0
begin
set @statement = '
use '+@target+'
SELECT TABLE_SCHEMA ,
TABLE_NAME ,
COLUMN_NAME ,
ORDINAL_POSITION ,
COLUMN_DEFAULT ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
NUMERIC_PRECISION ,
NUMERIC_PRECISION_RADIX ,
NUMERIC_SCALE ,
DATETIME_PRECISION,
TABLE_CATALOG
FROM INFORMATION_SCHEMA.COLUMNS'
EXEC (@statement)
fetch next from @schemas INTO @target
end
close @schemas;
deallocate @schemas
drop table #temporaltable
Upvotes: 1