Tuesday, May 5, 2015

MSSQL_Refresh-ListTableColumnsInfo

SELECT
SysObj.name AS TableName
,SysCol.column_id AS ColumnId
,SysCol.name AS ColumnName
,CASE
WHEN SysCol.is_identity=1
THEN 'Yes'
ELSE 'No'
END AS IsIdentity
,CASE
WHEN SysCol.is_nullable=1
THEN 'Yes'
ELSE 'No'
END AS IsNullable
,CASE
WHEN SysTyp.name IN ('char','varchar')
THEN SysTyp.name+'('+ CASE
WHEN SysCol.max_length<0
THEN 'MAX'
ELSE CONVERT(VARCHAR(10), SysCol.max_length)
END+')'
WHEN SysTyp.name IN ('nvarchar','nchar')
THEN SysTyp.name+'('+ CASE
WHEN SysCol.max_length<0
THEN 'MAX'
ELSE CONVERT(VARCHAR(10),SysCol.max_length/2)
END+')'
WHEN SysTyp.name IN ('numeric')
THEN SysTyp.name+'('+CONVERT(VARCHAR(10),SysCol.precision)+','+CONVERT(VARCHAR(10),SysCol.scale)+')'
ELSE SysTyp.name
END AS ColumnType
,SysCol.max_length
,SysCol.scale AS Scale
,SysCol.precision AS PrecisionFormat
,SysCol.collation_name AS Collation
,SysInf.CHARACTER_SET_NAME
FROM
B2BEMPowerdb_Refactor.sys.columns AS SysCol
INNER JOIN
B2BEMPowerdb_Refactor.sys.types AS SysTyp
ON SysCol.system_type_id=SysTyp.user_type_id AND SysTyp.is_user_defined=0
INNER JOIN
B2BEMPowerdb_Refactor.sys.objects AS SysObj
ON SysCol.object_id=SysObj.object_id
INNER JOIN
B2BEMPowerdb_Refactor.sys.schemas AS SysSch
ON SysObj.schema_id=SysSch.schema_id
LEFT OUTER JOIN
B2BEMPowerdb_Refactor.sys.identity_columns AS SysIdc
ON SysCol.object_id=SysIdc.object_id AND SysCol.column_id=SysIdc.column_id
LEFT OUTER JOIN
B2BEMPowerdb_Refactor.sys.computed_columns AS SysCpc
ON SysCol.object_id=SysCpc.object_id AND SysCol.column_id=SysCpc.column_id
LEFT OUTER JOIN
B2BEMPowerdb_Refactor.sys.check_constraints AS SysCkc
ON SysCol.object_id=SysCkc.parent_object_id AND SysCol.column_id=SysCkc.parent_column_id
LEFT OUTER JOIN
B2BEMPowerdb_Refactor.INFORMATION_SCHEMA.COLUMNS AS SysInf
ON SysCol.name=SysInf.COLUMN_NAME
ORDER BY
SysSch.name+'.'+SysObj.name,SysCol.column_id

HTMLCode

HTMLCode Content