Musings on code and life

Find Columns Query

I cobbled together some sql that would let me find a column in any database within a given Sql Server instance


DECLARE @ColumnName varchar(MAX) = '%col%'
DECLARE @TableName nvarchar(200) = '%'
DECLARE @DbName nvarchar(200) = '%db%'
DECLARE @SchemaName nvarchar(200) = '%'

--REF: http://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set
--REF: http://www.sqlservercentral.com/Forums/Topic438415-266-1.aspx
--REF: http://stackoverflow.com/questions/2729126/how-to-find-column-names-for-all-tables-in-all-databases-in-sql-server

DECLARE @AllColumns TABLE (
DbName sysname, 
SchemaName sysname, 
TableName sysname, 
ColumnName sysname,
DataType varchar(MAX),
Nullable varchar(MAX),
MiscInfo varchar(MAX)
)

SET NOCOUNT ON

DECLARE @SQL varchar(MAX) = '
USE [?]
SELECT 
''?'',
s.name AS schema_name,
t.name AS table_name,
c.name AS column_name,
CASE
WHEN d.name IN (''char'',''varchar'') THEN t.name+''(''+CASE WHEN c.max_length<0 then ''MAX'' ELSE CONVERT(varchar(10),c.max_length) END+'')''
WHEN d.name IN (''nvarchar'',''nchar'') THEN t.name+''(''+CASE WHEN c.max_length<0 then ''MAX'' ELSE CONVERT(varchar(10),c.max_length/2) END+'')''
WHEN d.name IN (''numeric'') THEN t.name+''(''+CONVERT(varchar(10),c.precision)+'',''+CONVERT(varchar(10),c.scale)+'')''
ELSE d.name
END AS DataType,
CASE 
WHEN c.is_nullable=1 THEN ''NULL''
ELSE ''NOT NULL''
END AS Nullable,
CASE
WHEN ic.column_id IS NULL THEN ''''
ELSE '' identity(''+ISNULL(CONVERT(varchar(10),ic.seed_value),'''')+'',''+ISNULL(CONVERT(varchar(10),ic.increment_value),'''')+'')=''+ISNULL(CONVERT(varchar(10),ic.last_value),''null'')
END
+CASE
WHEN sc.column_id IS NULL THEN ''''
ELSE '' computed(''+ISNULL(sc.definition,'''')+'')''
END
+CASE
WHEN cc.object_id IS NULL THEN ''''
ELSE '' check(''+ISNULL(cc.definition,'''')+'')''
END
AS MiscInfo
FROM sys.columns c
INNER JOIN sys.tables t ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
INNER JOIN sys.types d ON c.system_type_id = d.user_type_id AND d.is_user_defined=0
LEFT OUTER JOIN sys.identity_columns  ic ON c.object_id=ic.object_id AND c.column_id=ic.column_id
LEFT OUTER JOIN sys.computed_columns  sc ON c.object_id=sc.object_id AND c.column_id=sc.column_id
LEFT OUTER JOIN sys.check_constraints cc ON c.object_id=cc.parent_object_id AND c.column_id=cc.parent_column_id
WHERE DB_ID(''?'') > 4 --user db only
AND t.is_ms_shipped = 0 --user table only
AND ''?'' LIKE '''+@DbName+''' 
AND s.name LIKE '''+@SchemaName+''' 
AND t.name LIKE '''+@TableName+'''
AND c.name LIKE '''+@ColumnName+'''
'

INSERT INTO @AllColumns (DbName, SchemaName, TableName, ColumnName, DataType, Nullable, MiscInfo)
EXEC sp_msforeachdb @SQL

SET NOCOUNT OFF

SELECT * FROM @AllColumns