1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| SELECT col.name AS name,
t.name AS type,
Cast(col.length AS VARCHAR(80)) AS length,
Cast(col.xprec AS VARCHAR(80)) AS precision,
CASE
WHEN EXISTS (SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik
ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc
ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so
ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid) THEN 'YES'
ELSE 'NO'
END AS 'key',
CASE
WHEN col.isnullable = 1 THEN 'NO'
ELSE 'YES'
END AS isnullable,
Isnull(ep.[value], NULL) AS comment
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t
ON col.xtype = t.xusertype
LEFT JOIN sys.extended_properties ep
ON col.id = ep.major_id
AND col.colid = ep.minor_id
INNER JOIN dbo.sysobjects obj
ON col.id = obj.id
AND obj.status >= 0
LEFT JOIN sys.schemas s
ON obj.uid = s.schema_id
WHERE s.name = 'schema_name'
AND obj.name = ( 'table_name' );
|
