Finding databases, tables and/or columns


Where I work, there are a large number of databases, some which have an inordinate number of tables and columns. If you work with an ERP, you probably have the same situation where you are.

On many occasions, I need to find all the tables that have a column related to parts, or all the databases with a log table, or tables with combinations of columns.

The following query lets me do that:

DECLARE @search    VARCHAR(200); SET @search = '[MyDbName].[dbo].[MyTable].[MyColumn]';
DECLARE @withColumn VARCHAR(200); SET @withColumn = NULL;
DECLARE @commonTo   VARCHAR(200); SET @commonTo   = NULL;
/* Acceptable forms for @search are:
     '[db].[schema].[table].[col]' - show information for specific column(s)
     '[db].[schema].[table]'       - show information for specific table(s)
     '[db].[table].[col]'          - show information for specific column(s)
     '[db].[table]'                - show information for specific table(s)
   where
      use of [ and ] around any element is optional
      all elements are optional and default to '%' (select all)
      periods are mandatory (i.e. there must be 1, 2 or 3 periods in the search)
      db     = a specific db name 
                 (e.g. '[PRODDB].[CUSTOMER].[CITY]' 
                       display [CUSTOMER].[CITY] in the PRODDB database)
         - a like expression 
                 (e.g. '%TEST%.[CUSTOMER].[CITY]' 
                       display [CUSTOMER].[CITY] in any test database)
      schema = a recognized schema name 
                 (e.g. '[PRODDB].[DBO].[CUSTOMER].[CITY]'
                       display [CUSTOMER].[CITY] in the DBO schema of the PRODDB 
                       database)
           Note: currently, the only recognized schema name is 'DBO'.
           do NOT use '%' when also omitting the column; instead also omit the
                 schema (e.g. 'PRODDB.CUSTOMER', not 'PRODDB.%.CUSTOMER')
      table  = a specific table name 
                 (e.g. '.[CUSTOMER]'
                       display all occurrences of the CUSTOMER table in all
                       databases)
         - a LIKE expression 
                 (e.g. '[PRODDB].[%CUST%]'
                       display all customer related tables in the PRODDB database)
      col    = a specific column name 
                 (e.g. 'PRODDB.CUSTOMER.CITY'
                       display CITY column from the PRODDB.CUSTOMER table)
         - a LIKE expression 
                 (e.g. 'PRODDB.CUSTOMER.%COUNT'
                       display all counter related columns in the PRODDB.CUSTOMER
                       table)
  @withColumn can be NULL, a specific column name or a like expression. 
    When @withColumn is non-NULL, then only elements from tables which also 
    have a column matching @withColumn will be reported.
    E.g.
	SET @search = '[PRODDB].[%CUST%].[balance]'; SET @withColumn = NULL;
	    reports all customer related tables with a 'balance' column.
	SET @search = '[PRODDB].[%CUST%].[balance]'; SET @withColumn = '%audit%';
	    reports all customer related tables that have a 'balance'column and 
            an audit related column.
  @commonTo can be NULL, or the unqualified name of a table. 
    When @commonTo is non-NULL, then only elements from that are common to the 
    table being reported and the table indicated by commonTo (in the same 
    database) are reported.
    E.g.
      SET @search = '[PRODDB].[CUSTOMER].'; SET @commonTo = NULL;
	  reports all columns in the CUSTOMER table.
      SET @search = '[PRODDB].[CUSTOMER].'; SET @commonTo = 'PERSON';
	  reports only columns that exist in both PRODDB.CUSTOMER and PRODDB.PERSON.
*/

---------------------------------
/* internal settings: */
-- Set @showSelect to 'Y' to see the constructed select statement
DECLARE @showSelect CHAR(1); SET @showSelect = 'N'; 
-- Set @forceRows to:
-- T = include row count information when displaying tables only. 
-- Y = show for tables and columns. 
-- N = never show.
DECLARE @forceRows  CHAR(1); SET @forceRows = 'T'; 

DECLARE @db     VARCHAR(50)
       ,@schema VARCHAR(50)
       ,@table  VARCHAR(50)
       ,@column VARCHAR(50)
       ,@sepa   INT
       ,@sepb   INT;

SELECT @search = LTRIM(RTRIM(REPLACE(REPLACE(@search,'[',''),']',''))) + 
                 CASE WHEN (RIGHT(LTRIM(RTRIM(@search)),1))='.'THEN'%...'ELSE'...'END
      ,@sepa   = CHARINDEX('.',@search)
      ,@db     = LEFT(@search,@sepa - 1)
      ,@sepb   = CHARINDEX('.',@search,@sepa+1)
      ,@schema = SUBSTRING(@search,@sepa+1,@sepb-@sepa-1)
      ,@sepa   = CHARINDEX('.',@search,@sepb+1)
      ,@table  = SUBSTRING(@search,@sepb+1,@sepa-@sepb-1)
      ,@sepb   = CHARINDEX('.',@search,@sepa+1)
      ,@column = SUBSTRING(@search,@sepa+1,@sepb-@sepa-1)
      ,@sepa   = CASE WHEN (@schema IN ('dbo')) THEN 1 ELSE LEN(@column) END
      ,@column = CASE WHEN (@sepa = 0) THEN @table ELSE @column END
      ,@table  = CASE WHEN (@sepa = 0 ) THEN @schema ELSE @table END
      ,@schema = CASE WHEN (@sepa = 0) THEN '' ELSE @schema END; 

DECLARE @dbInfo TABLE ( [sequence]       INT
                       ,[dbname]         NVARCHAR(MAX)
                       ,[dbschema]       NVARCHAR(MAX)
                       ,[tablename]      NVARCHAR(MAX)
 		       ,[tabletype]      NVARCHAR(MAX)
                       ,[columnname]     NVARCHAR(MAX)
                       ,[datatype]       NVARCHAR(MAX)
                       ,[maxlen]         NVARCHAR(MAX)
                       ,[precision]      NVARCHAR(MAX)
                       ,[scale]          NVARCHAR(MAX)
                       ,[default]        NVARCHAR(MAX)
                       ,[rows]           INT
                      );

DECLARE @execQualifiers VARCHAR(MAX)
       ,@rowSelection   VARCHAR(MAX);

SELECT @execQualifiers = CASE
                           WHEN ISNULL(@db,'') = '' THEN ''
                           ELSE ' AND [IS].[TABLE_CATALOG] LIKE '''+@db+''''
                         END +
                         CASE
                           WHEN ISNULL(@schema,'') = '' THEN ''
                           ELSE ' AND [IS].[TABLE_SCHEMA] LIKE '''+@schema+''''
                         END +
                         CASE
                           WHEN ISNULL(@table,'') = '' THEN ''
                           ELSE ' AND [IS].[TABLE_NAME] LIKE '''+@table+''''
                         END +
                         CASE
                           WHEN ISNULL(@column,'') = '' THEN ''
                           ELSE ' AND [IS].[COLUMN_NAME] LIKE '''+@column+''''
                         END +
                         CASE
                           WHEN (ISNULL(@withColumn,'') = '') THEN ''
                           ELSE ' AND EXISTS(SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] ' +
                                'AS [IS2] WHERE [IS2].[TABLE_CATALOG] = [IS].[TABLE_CATALOG]' +
			        '  AND [IS2].[TABLE_NAME] = [IS].[TABLE_NAME] ' +
				'  AND [IS2].[COLUMN_NAME] LIKE ''' + @withColumn + ''')'
                         END +
			 CASE
			   WHEN (ISNULL(@commonTo,'') = '') THEN ''
			   ELSE ' AND EXISTS(SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] AS' +
                                ' [IS3] ' +
			        'WHERE [IS3].[TABLE_CATALOG] = [IS].[TABLE_CATALOG] ' +
				'  AND [IS3].[TABLE_NAME] = ''' + @commonTo + ''' ' +
				'  AND [IS3].[COLUMN_NAME] = [IS].[COLUMN_NAME])'
			 END
        ,@rowSelection = CASE 
	                   WHEN ISNULL(@forceRows,'') = 'Y' 
		  	     OR (ISNULL(@forceRows,'') = 'T' AND ISNULL(@column,'') = '') THEN 
			        '(SELECT SUM([ST].[row_count])' +
	                        '   FROM [sys].[dm_db_partition_stats] AS [ST]' +
  	                        '  WHERE (object_name(object_id) = [IS].[TABLE_NAME])' +
			        '        AND ([ST].[index_id] < 2))'
	                   ELSE 'NULL'
		         END;

DECLARE @execStr VARCHAR(MAX);
DECLARE @collate VARCHAR(64); 
SET @collate = 'COLLATE SQL_Latin1_General_CP1_CI_AS';
SET @execStr = 
  'USE ?;' +
  'SELECT [IS].[ORDINAL_POSITION]         AS [sequence]' +
  '      ,[IS].[TABLE_CATALOG]            AS [dbname]' +
  '      ,[IS].[TABLE_SCHEMA]             AS [dbschema]' + 
  '      ,[IS].[TABLE_NAME]               AS [tablename]' +
  '      ,[IT].[TABLE_TYPE]               AS [tabletype]' +
  '      ,[IS].[COLUMN_NAME]              AS [columnname]' +
  '      ,[IS].[DATA_TYPE]                AS [datatype]' +
  '      ,[IS].[CHARACTER_MAXIMUM_LENGTH] AS [maxlen]' +
  '      ,[IS].[NUMERIC_PRECISION]        AS [precision]' +
  '      ,[IS].[NUMERIC_SCALE]            AS [scale]' +
  '      ,[IS].[COLUMN_DEFAULT]           AS [default]' +
  '      ,' + @rowSelection + '           AS [rows]' +
  '  FROM [INFORMATION_SCHEMA].[COLUMNS] AS [IS]' +
  '       JOIN [INFORMATION_SCHEMA].[TABLES] AS [IT]' +
  '         ON [IT].[TABLE_NAME] = [IS].[TABLE_NAME]' +
  '       JOIN [sys].[sysdatabases] [SS]' +
  '         ON [SS].[name] ' + @collate + ' = [IS].[TABLE_CATALOG] ' + @collate +
  ' WHERE [SS].[sid] <> 1' +
  @execQualifiers +
  ';';

IF @showSelect = 'Y'
SELECT @search                               AS [search]
      ,@db                                   AS [db]
      ,@schema                               AS [schema]
      ,@table                                AS [table]
      ,@column                               AS [column]
      ,SUBSTRING(@execStr, 7, LEN(@execStr)) AS [execStr];

INSERT INTO @dbInfo
EXEC [sp_msforeachdb] @execStr;

IF ISNULL(@column,'') = '' 
BEGIN 
  SELECT sequence
        ,dbname
        ,dbschema
        ,tablename
        ,'[' + dbname +
         '].[' + dbschema +
         '].[' + tablename +']'                      AS [Qualified Table Name]
	,tabletype                                   AS [Type]
	,CASE 
	   WHEN Rows IS NULL THEN ''
	   ELSE CAST(Rows AS VARCHAR(10)) + ' rows ' 
	 END                                         AS [Comments]
    FROM @DBINFO
   WHERE sequence = 1
   ORDER BY [dbname]
           ,[dbschema]
           ,[tablename];
END
ELSE
BEGIN 
  SELECT sequence
        ,dbname
        ,dbschema
        ,tablename
        ,columnname
        ,'[' + dbname +
         '].[' + dbschema +
         '].[' + tablename +']'                          AS [Qualified Table Name]
	,tabletype                                       AS [Type]
        ,'[' + dbname +
         '].[' + dbschema +
         '].[' + tablename +
         '].[' + columnname + ']'                        AS [Qualified Identifier]
        ,UPPER([datatype]) +
         CASE
  	   WHEN ([datatype] LIKE '%char')             THEN '('+[maxlen]+')'   
           WHEN ([datatype] LIKE '%int')                                                   
             OR ([datatype] LIKE '%binary')
             OR ([datatype] IN ('float','real','datetime_%','time')) 
			                              THEN '('+[precision]+')'    
           WHEN ([datatype] IN ('decimal','numeric')) THEN '('+[precision]+', '+[scale]+')'
	   WHEN ([datatype] = 'xml')                  THEN '(???)'   
           ELSE                                            ''  
         END                                             AS [DataType]
        ,'DECLARE @' +
         REPLACE([columnname],' ','_') + ' ' + 
         UPPER([datatype]) +
         CASE
  	   WHEN ([datatype] LIKE '%char')             THEN '('+[maxlen]+')'   
           WHEN ([datatype] LIKE '%int')                                                   
             OR ([datatype] LIKE '%binary')
             OR ([datatype] IN ('float','real','datetime_%','time')) 
			                              THEN '('+[precision]+')'    
           WHEN ([datatype] IN ('decimal','numeric')) THEN '('+[precision]+', '+[scale]+')'
	   WHEN ([datatype] = 'xml')                  THEN '(???)'   
           ELSE                                            ''  
         END +
         ';'                                             AS [local declaration]
        ,CASE 
           WHEN rows IS NULL THEN ''
           ELSE CAST(rows AS VARCHAR(10)) + ' rows ' 
         END                                             AS [Comments]
    FROM @DBINFO
   ORDER BY [dbname]
           ,[dbschema]
           ,[tablename]
           ,[sequence];
END;

So to find the all the columns related to parts, I would change the SET statement at the top to

SET @search = '..%part%';

and then execute the script.

To get a list of all the log tables:

SET @search = '.%log';

Or perhaps, I need all columns from all historical data tables in the production database where the table has a customer number column:

SET @search = 'proddb..hist%.'; SET @andColumn = '%custno%';
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s