Sunday, October 16, 2011

How to know SQL table Properties

SELECT O.name AS TableName, C.Name As ColumnName,T.name AS DateType,T.max_length AS [Length], T.precision AS Precesion, T.scale AS Scale,

CASE WHEN C.is_identity = 1 THEN 'Y' ELSE 'N' END AS [Identity],

CASE WHEN C.is_nullable = 1 THEN 'Y' ELSE 'N' END AS Nullable

FROM SYS.OBJECTS O

INNER JOIN SYS.columns C ON C.object_id = O.object_id

INNER JOIN SYS.types T ON T.system_type_id = C.system_type_id

WHERE TYPE = 'U' AND T.name <> 'SYSNAME'

AND O.name <>'sysdiagrams'

ORDER BY O.name, C.column_id