Find a column in SQL database tables

SELECT s.NAME AS ColumnName
 ,sh.NAME + '.' + o.NAME AS ObjectName
 ,o.type_desc AS ObjectType
 ,CASE 
  WHEN t.NAME IN (
    'char'
    ,'varchar'
    )
   THEN t.NAME + '(' + CASE 
     WHEN s.max_length < 0
      THEN 'MAX'
     ELSE CONVERT(VARCHAR(10), s.max_length)
     END + ')'
  WHEN t.NAME IN (
    'nvarchar'
    ,'nchar'
    )
   THEN t.NAME + '(' + CASE 
     WHEN s.max_length < 0
      THEN 'MAX'
     ELSE CONVERT(VARCHAR(10), s.max_length / 2)
     END + ')'
  WHEN t.NAME IN ('numeric')
   THEN t.NAME + '(' + CONVERT(VARCHAR(10), s.precision) + ',' + CONVERT(VARCHAR(10), s.scale) + ')'
  ELSE t.NAME
  END AS DataType
 ,CASE 
  WHEN s.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
 ,CASE 
  WHEN t.NAME IN (
    'char'
    ,'varchar'
    )
   THEN CASE 
     WHEN s.max_length < 0
      THEN 0
     ELSE CONVERT(VARCHAR(10), s.max_length)
     END
  WHEN t.NAME IN (
    'nvarchar'
    ,'nchar'
    )
   THEN CASE 
     WHEN s.max_length < 0
      THEN 0
     ELSE CONVERT(VARCHAR(10), s.max_length / 2)
     END
  END val
INTO #temp
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id = t.user_type_id
 AND t.is_user_defined = 0
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas sh ON o.schema_id = sh.schema_id
LEFT JOIN sys.identity_columns ic ON s.object_id = ic.object_id
 AND s.column_id = ic.column_id
LEFT JOIN sys.computed_columns sc ON s.object_id = sc.object_id
 AND s.column_id = sc.column_id
LEFT JOIN sys.check_constraints cc ON s.object_id = cc.parent_object_id
 AND s.column_id = cc.parent_column_id
WHERE --t.name in ('nvarchar','nchar','char','varchar')
 S.NAME LIKE 'attendance%' --<--Write your columnname here
ORDER BY sh.NAME + '.' + o.NAME
 ,s.column_id

SELECT *
FROM #temp --where val>500

DROP TABLE #temp

No comments:

Post a Comment