SQL Server'da bir tabloya ait kolon bilgilerinin alınması. 3 farklı seçenek.

SQL Server'da bir tabloya ait kolon bilgilerinin alınması. 3 farklı seçenek.

Seçenek 1:

SELECT ISNULL(COL.COLUMN_NAME,'') as Name

    ,ISNULL(COL.DATA_TYPE, '') as Type

    ,ISNULL(COL.NUMERIC_SCALE,0)  as Numeric_Scale  

    ,ISNULL(COL.NUMERIC_PRECISION, 0) as Precision  

    ,ISNULL(COL.CHARACTER_MAXIMUM_LENGTH, 0) as LenStr 

    ,CASE WHEN ISNULL(KEYCOLUSE.COLUMN_NAME,'0') = '0' THEN 0 ELSE 1 END  as isPrimaryKey

FROM INFORMATION_SCHEMA.COLUMNS as COL

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as KEYCOLUSE

ON KEYCOLUSE.TABLE_NAME = COL.TABLE_NAME

AND KEYCOLUSE.COLUMN_NAME = COL.COLUMN_NAME

WHERE COL.TABLE_NAME = 'TableName'

    AND COL.TABLE_SCHEMA = 'dbo'

ORDER BY COL.ORDINAL_POSITION

Seçenek 2:

SELECT C.name AS Name,

       T.name AS Type,

       ISNULL(C.scale, 0) AS Numeric_Scale,

       ISNULL(C.precision, 0) AS Precision,

       (CASE WHEN T.name LIKE '%char%' THEN ISNULL(C.max_length, 0) ELSE 0 END) AS LenStr,

       (CASE WHEN ISNULL(P.column_id,0)>0 THEN 1 ELSE 0 END) As isPrimaryKey

FROM sys.columns AS C

LEFT JOIN (

             SELECT  ic.object_id, ic.column_id

             FROM    sys.indexes AS i INNER JOIN

             sys.index_columns AS ic ON  i.object_id = ic.object_id AND i.index_id = ic.index_id

             WHERE   i.is_primary_key = 1

) AS P ON C.object_id = P.object_id AND C.column_id = P.column_id

INNER JOIN sys.types T on T.system_type_id = C.system_type_id

WHERE OBJECT_NAME(C.object_id) = 'TableName'

ORDER BY C.column_id 

Seçenek 3:

SELECT C.id,C.name  AS Name,

             T.name AS Type,

             ISNULL(C.scale, 0) AS Numeric_Scale,

             ISNULL(C.prec, 0) AS Precision,

             (CASE WHEN LOWER(T.name) LIKE '%char%' THEN ISNULL(C.length, 0) ELSE 0 END) AS LenStr,

             (CASE WHEN ISNULL(P.colid,0)>0 THEN 1 ELSE 0 END) As isPrimaryKey

FROM syscolumns AS C

LEFT JOIN (

                    SELECT  ic.id, ic.colid

                    FROM    sysindexkeys AS ic

) AS P ON C.id = P.id AND C.colid = P.colid

INNER JOIN systypes T on T.xtype = C.xtype AND T.status=0

WHERE OBJECT_NAME(C.id) = 'TableName'

ORDER BY C.colorder 

* How to find out column names of a table in SQL Server