-- Find columns by properties -- Transact-SQL script to find columns by properties like data type, collation etc. /* Sometimes it is for interessting in which tables columns with a specific name like 'PostalCode' or a user defined data type is used. Also it's requiered which columns uses a special collation. With this Transact-SQL script you can find all columns within a database by filtering on the column properties. Works with MS SQL Server 2005 and high versions in all edition. To use the new column properties in SQL Server 2008/2008R2 remove the comments. Requieres permissions to view meta data / definition and select permission on the referenced tables. */ -- Transact-SQL script to find columns by properties like data type, collation etc. DECLARE @true varchar(10), @false varchar(10); SET @false = ''; -- Display value for false / not set. SET @true = 'X'; -- Display value for true / set. SELECT SCH.name + '.' + TBL.name AS TableName ,COL.column_id AS ColumnID ,COL.name AS ColumnName ,STYP.name AS SystemType ,CASE WHEN COL.system_type_id = COL.user_type_id THEN '' ELSE TYP.name END AS UserType ,CASE WHEN COL.max_length = -1 THEN 'max' ELSE CONVERT(varchar(20), COL.max_length) END AS MaxLength ,COL.precision AS Precision ,COL.scale AS Scale ,COL.collation_name AS CollationName ,DFLT.name AS DefaultValue ,CASE WHEN COL.is_nullable = 0 THEN @false ELSE @true END AS IsNullable ,CASE WHEN COL.is_identity = 0 THEN @false ELSE @true END AS IsIdentity ,CASE WHEN COL.is_computed = 0 THEN @false ELSE @true END AS IsComputed ,CASE WHEN COL.is_xml_document = 0 THEN @false ELSE @true END AS IsXmlDoc ,CASE WHEN COL.is_replicated = 0 THEN @false ELSE @true END AS IsReplicated ,CASE WHEN COL.is_merge_published = 0 THEN @false ELSE @true END AS IsMergeRepl -- New Columns on 2008/2008R2; remove comment. --,CASE WHEN COL.is_filestream = 0 THEN @false ELSE @true END AS IsFileStream --,CASE WHEN COL.is_sparse = 0 THEN @false ELSE @true END AS IsSparse FROM sys.tables AS TBL INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id INNER JOIN sys.columns AS COL ON TBL.object_id = COL.object_id INNER JOIN sys.types AS STYP ON COL.system_type_id = STYP.system_type_id AND COL.system_type_id = STYP.user_type_id AND STYP.is_user_defined = 0 INNER JOIN sys.types AS TYP ON COL.system_type_id = TYP.system_type_id AND COL.user_type_id = TYP.user_type_id LEFT JOIN sys.objects AS DFLT ON COL.default_object_id = DFLT.object_id -- Option filter to find columns by properties WHERE TBL.is_ms_shipped = 0 -- No system tables shipped by MS. --AND NOT DFLT.name IS NULL -- Have a default value assigned. --AND STYP.name = 'uniqueidentifier' -- Filter on specific data type. --AND COL.max_length = -1 -- Lenght = max. --AND COL.is_identity = 1 -- All identity columns. ORDER BY TableName ,ColumnID
