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
* How to find out column names of a table in SQL Server
