CODEWITHSUNDEEP
sqlsql-server
webworm
webworm

Reputation: 11019

Get list of databases, tables, columns, and column datatypes

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

Answers (2)

Sean Lange
Sean Lange

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

Christian Bawman
Christian Bawman

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

Related Questions