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: do NOT use '%' as the schema 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';
-- Set @schemaList to a comma-delimited list of allowable db schemata
DECLARE @schemaList VARCHAR(100); SET @schemaList = 'dbo';
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 (CHARINDEX(','+@schema+',',','+@schemaList+',')
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 @withColumn = '%custno%';
Clive's YABB: lessons learned and promptly forgotten from the wonderful world of programming 