How do you list the primary key of a SQL Server table?
一个简单的问题，如何使用T-SQL列出表的主键？ 我知道如何获取表上的索引，但不记得如何获取PK。
1
2
3
4
5
6
7
8
| SELECT Col.Column_Name FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
AND Col.Table_Name = '<your table name>' |
现在通常建议在SQL Server中在INFORMATION_SCHEMA上使用sys.*视图，因此，除非您打算迁移数据库，否则我将使用这些视图。使用sys.*视图的方法如下：
1
2
3
4
5
6
7
8
9
| SELECT
c.name AS column_name,
i.name AS index_name,
c.is_identity
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
AND i.object_ID = OBJECT_ID('<schema>.<tablename>'); |
这是仅使用sys-tables的解决方案。
它列出了数据库中的所有主键。它为每个主键返回架构，表名，列名和正确的列排序顺序。
如果要获取特定表的主键，则需要在SchemaName和TableName上进行过滤。
恕我直言，此解决方案非常通用，并且不使用任何字符串文字，因此它将在任何计算机上运行。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| SELECT
s.name AS SchemaName,
t.name AS TableName,
tc.name AS ColumnName,
ic.key_ordinal AS KeyOrderNr
FROM
sys.schemas s
INNER JOIN sys.tables t ON s.schema_id=t.schema_id
INNER JOIN sys.indexes i ON t.object_id=i.object_id
INNER JOIN sys.index_columns ic ON i.object_id=ic.object_id
AND i.index_id=ic.index_id
INNER JOIN sys.columns tc ON ic.object_id=tc.object_id
AND ic.column_id=tc.column_id
WHERE i.is_primary_key=1
ORDER BY t.name, ic.key_ordinal ; |
这是使用sql查询问题获取表主键的另一种方法：
1
2
3
4
| SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = '<your table name>' |
它使用KEY_COLUMN_USAGE确定给定表的约束
然后使用OBJECTPROPERTY(id, 'IsPrimaryKey')确定每个键是否为主键
使用MS SQL Server，您可以执行以下操作：
1
2
3
| --List all tables primary keys
SELECT * FROM information_schema.table_constraints
WHERE constraint_type = 'Primary Key' |
如果需要特定的表，也可以在table_name列上进行过滤。
我喜欢INFORMATION_SCHEMA技术，但是我使用的另一种技术是：
exec sp_pkeys'表'
-这是另一个修改版本，也是相关查询的示例
1
2
3
4
5
6
7
8
| SELECT TC.TABLE_NAME AS [TABLE_NAME], TC.CONSTRAINT_NAME AS [Primary_Key]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.TABLE_NAME IN
(SELECT [NAME] AS [TABLE_NAME] FROM SYS.OBJECTS
WHERE TYPE = 'U') |
这应该列出所有约束(主键和外键)，并在查询末尾放置表名
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
| /* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME='' ,
REFERENCE_COL_NAME=''
FROM sys.key_constraints AS PKnUKEY
INNER JOIN sys.tables AS PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns AS PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns AS PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=PKnUTable.name
AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE='FK',
PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint
ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
INNER JOIN sys.sysobjects oReference
ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
)
SELECT * FROM ALL_KEYS_IN_TABLE
WHERE
PARENT_TABLE_NAME IN ('YOUR_TABLE_NAME')
OR REFERENCE_TABLE_NAME IN ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME; |
作为参考，请通读-http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx
谢了，兄弟们。
略有变化，我用它来查找所有表的所有主键。
1
2
3
4
5
6
7
8
9
| SELECT A.Name,Col.Column_Name FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ,
(SELECT NAME FROM dbo.sysobjects WHERE xtype='u') AS A
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY '
AND Col.Table_Name = A.Name |
1
2
3
| SELECT A.TABLE_NAME AS [TABLE_NAME], A.CONSTRAINT_NAME AS [Primary_Key]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME |
系统存储过程sp_help将为您提供信息。执行以下语句：
1
| EXECUTE sp_help TABLE_NAME |
此列为您提供了PK列。
1
| SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName' |
下面的查询将列出特定表的主键：
1
2
3
4
5
6
7
8
| SELECT DISTINCT
CONSTRAINT_NAME AS [CONSTRAINT],
TABLE_SCHEMA AS [Schema],
TABLE_NAME AS TableName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = 'mytablename' |
我告诉一个简单的技术，我遵循
运行此代码作为查询。在要知道其主键的table_name位置提及您的表名(不要忘记单引号)。结果将显示为附件图像。希望对您有帮助
对于给定的TableName和Schema，以逗号分隔的主键列列表：
1
2
3
4
5
6
| SELECT DISTINCT SUBSTRING ( stuff(( SELECT DISTINCT ',' + [COLUMN_NAME]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'
ORDER BY 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'' )
,2,9999) |
此版本显示模式，表名以及以逗号分隔的有序主键列表。 Object_Id()对链接服务器不起作用，因此我们按表名进行过滤。
没有REPLACE(Si1.Column_Name，``，'')，它将在我正在测试的数据库上显示Column_Name的xml开头和结尾标记。我不确定为什么数据库需要替换" Column_Name"，所以如果有人知道，请发表评论。
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
| DECLARE @TableName VARCHAR(100) = '';
WITH Sysinfo
AS (SELECT Kcu.Table_Name
, Kcu.Table_Schema AS Schema_Name
, Kcu.Column_Name
, Kcu.Ordinal_Position
FROM [LinkServer].Information_Schema.Key_Column_Usage Kcu
JOIN [LinkServer].Information_Schema.Table_Constraints AS Tc ON Tc.Constraint_Name = Kcu.Constraint_Name
WHERE Tc.Constraint_Type = 'Primary Key')
SELECT Schema_Name
,TABLE_NAME
, STUFF(
(
SELECT ', '
, REPLACE(Si1.Column_Name, '', '')
FROM Sysinfo Si1
WHERE Si1.Table_Name = Si2.Table_Name
ORDER BY Si1.Table_Name
, Si1.Ordinal_Position
FOR XML PATH('')
), 1, 2, '') AS Primary_Keys
FROM Sysinfo Si2
WHERE TABLE_NAME = CASE
WHEN @TableName NOT IN( '', 'All')
THEN @TableName
ELSE TABLE_NAME
END
GROUP BY Si2.Table_Name, Si2.Schema_Name; |
和使用乔治查询的相同模式：
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
| DECLARE @TableName VARCHAR(100) = '';
WITH Sysinfo
AS (SELECT S.Name AS Schema_Name
, T.Name AS TABLE_NAME
, Tc.Name AS Column_Name
, Ic.Key_Ordinal AS Ordinal_Position
FROM [LinkServer].Sys.Schemas S
JOIN [LinkServer].Sys.Tables T ON S.Schema_Id = T.Schema_Id
JOIN [LinkServer].Sys.Indexes I ON T.Object_Id = I.Object_Id
JOIN [LinkServer].Sys.Index_Columns Ic ON I.Object_Id = Ic.Object_Id
AND I.Index_Id = Ic.Index_Id
JOIN [LinkServer].Sys.Columns Tc ON Ic.Object_Id = Tc.Object_Id
AND Ic.Column_Id = Tc.Column_Id
WHERE I.Is_Primary_Key = 1)
SELECT Schema_Name
,TABLE_NAME
, STUFF(
(
SELECT ', '
, REPLACE(Si1.Column_Name, '', '')
FROM Sysinfo Si1
WHERE Si1.Table_Name = Si2.Table_Name
ORDER BY Si1.Table_Name
, Si1.Ordinal_Position
FOR XML PATH('')
), 1, 2, '') AS Primary_Keys
FROM Sysinfo Si2
WHERE TABLE_NAME = CASE
WHEN @TableName NOT IN('', 'All')
THEN @TableName
ELSE TABLE_NAME
END
GROUP BY Si2.Table_Name, Si2.Schema_Name; |
Sys.Objects Table contains row for each user-defined, schema-scoped
object .
Constraints created like Primary Key or others will be the object and
Table name will be the parent_object
Query sys.Objects and collect the Object's Ids of Required Type
1
2
3
4
5
6
| DECLARE @TableName nvarchar(50)='TblInvoice' -- your table name
DECLARE @TypeOfKey nvarchar(50)='PK' -- For Primary key
SELECT Name FROM sys.objects
WHERE TYPE = @TypeOfKey
AND parent_object_id = OBJECT_ID (@TableName) |
我可以为下面的原始问题提出一个更准确的简单答案
1
2
3
4
5
6
7
8
| SELECT
KEYS.table_schema, KEYS.table_name, KEYS.column_name, KEYS.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KEYS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON cons.TABLE_SCHEMA = KEYS.TABLE_SCHEMA
AND cons.TABLE_NAME = KEYS.TABLE_NAME
AND cons.CONSTRAINT_NAME = KEYS.CONSTRAINT_NAME
WHERE cons.CONSTRAINT_TYPE = 'PRIMARY KEY' |
笔记：
上面的一些答案缺少仅针对主键的过滤器
列！
我在CTE中使用以下内容加入较大的专栏
列出以提供源中的元数据，以提供过渡表和SSIS代码的BIML生成
可能会在最近发布，但希望这可以通过使用以下t-sql查询来帮助某人查看sql server中的主键列表：
1
2
3
4
5
6
7
8
| SELECT schema_name(t.schema_id) AS [schema_name], t.name AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS PrimaryKeyColumnName,
i.name AS PrimaryKeyConstraintName
FROM sys.tables t
INNER JOIN sys.indexes AS i ON t.object_id=i.object_id
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE OBJECT_NAME(ic.OBJECT_ID) = 'YourTableNameHere' |
如果需要，可以使用此查询查看所有外键的列表：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| SELECT
f.name AS ForeignKeyConstraintName
,OBJECT_NAME(f.parent_object_id) AS ReferencingTableName
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferencingColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS
ReferencedColumnName ,delete_referential_action_desc AS
DeleteReferentialActionDesc ,update_referential_action_desc AS
UpdateReferentialActionDesc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
--WHERE OBJECT_NAME(f.parent_object_id) = 'YourTableNameHere'
--If you want to know referecing table details
WHERE OBJECT_NAME(f.referenced_object_id) = 'YourTableNameHere'
--If you want to know refereced table details
ORDER BY f.name |
我从朋友那里发现了这一点，如果您要在特定模式下查找表的所有主键，这将非常有效。
1
2
3
4
5
| SELECT tc.constraint_name AS IndexName,tc.table_name AS TableName,tc.table_schema
AS SchemaName,kc.column_name AS COLUMN_NAME
FROM information_schema.table_constraints tc,information_schema.key_column_usage kc
WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.table_name = tc.table_name AND kc.table_schema = tc.table_schema
AND kc.constraint_name = tc.constraint_name AND tc.table_schema='<SCHEMA_NAME>' |
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
| SELECT t.name AS 'table', i.name AS 'index', it.xtype,
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 1
AND k.id = t.id)
AS 'column1',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 2
AND k.id = t.id)
AS 'column2',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 3
AND k.id = t.id)
AS 'column3',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 4
AND k.id = t.id)
AS 'column4',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 5
AND k.id = t.id)
AS 'column5',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 6
AND k.id = t.id)
AS 'column6',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 7
AND k.id = t.id)
AS 'column7',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 8
AND k.id = t.id)
AS 'column8',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 9
AND k.id = t.id)
AS 'column9',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 10
AND k.id = t.id)
AS 'column10',
FROM sysobjects t
INNER JOIN sysindexes i ON i.id = t.id
INNER JOIN sysobjects it ON it.parent_obj = t.id AND it.name = i.name
WHERE it.xtype = 'PK'
ORDER BY t.name, i.name |
试试看：
1
2
3
4
5
6
7
| SELECT
CONSTRAINT_CATALOG AS DataBaseName,
CONSTRAINT_SCHEMA AS SchemaName,
TABLE_NAME AS TableName,
CONSTRAINT_Name AS PrimaryKey
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'Primary Key' AND TABLE_NAME = 'YourTable' |
如果您要执行自己的ORM或从给定表中生成代码，则可能是您正在寻找的形式：
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
39
40
41
42
| DECLARE @TABLE VARCHAR(100) = 'mytable';
WITH cte AS
(
SELECT
tc.CONSTRAINT_SCHEMA
, tc.CONSTRAINT_TYPE
, tc.TABLE_NAME
, ccu.COLUMN_NAME
, IS_NULLABLE
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.TABLE_NAME=ccu.TABLE_NAME AND tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA
INNER JOIN information_schema.COLUMNS c ON ccu.COLUMN_NAME=c.COLUMN_NAME AND ccu.TABLE_NAME=c.TABLE_NAME AND ccu.TABLE_SCHEMA=c.TABLE_SCHEMA
WHERE
tc.table_name=@TABLE
AND
ccu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
UNION
SELECT TABLE_SCHEMA,'COLUMN', TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLE
AND COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @TABLE)
)
SELECT
CAST(iif(CONSTRAINT_TYPE='PRIMARY KEY',1,0) AS bit) PrimaryKey
,CAST(iif(CONSTRAINT_TYPE='FOREIGN KEY',1,0) AS bit) ForeignKey
,CAST(iif(CONSTRAINT_TYPE='COLUMN',1,0) AS bit) NotKey
,COLUMN_NAME
,CAST(iif(is_nullable='NO',0,1) AS bit) IsNullable
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
FROM
cte
ORDER BY
CASE CONSTRAINT_TYPE
WHEN 'PRIMARY KEY' THEN 1
WHEN 'FOREIGN KEY' THEN 2
ELSE 3 END
, COLUMN_NAME |
结果如下所示：
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
| <TABLE cellspacing=0 border=1>
<tr>
<td STYLE=min-width:50px>PrimaryKey</td>
<td STYLE=min-width:50px>ForeignKey</td>
<td STYLE=min-width:50px>NotKey</td>
<td STYLE=min-width:50px>COLUMN_NAME</td>
<td STYLE=min-width:50px>IsNullable</td>
<td STYLE=min-width:50px>DATA_TYPE</td>
<td STYLE=min-width:50px>CHARACTER_MAXIMUM_LENGTH</td>
<td STYLE=min-width:50px>NUMERIC_PRECISION</td>
</tr>
<tr>
<td STYLE=min-width:50px>1</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>LectureNoteID</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>int</td>
<td STYLE=min-width:50px>NULL</td>
<td STYLE=min-width:50px>10</td>
</tr>
<tr>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>1</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>LectureId</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>int</td>
<td STYLE=min-width:50px>NULL</td>
<td STYLE=min-width:50px>10</td>
</tr>
<tr>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>1</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>NoteTypeID</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>int</td>
<td STYLE=min-width:50px>NULL</td>
<td STYLE=min-width:50px>10</td>
</tr>
<tr>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>1</td>
<td STYLE=min-width:50px>Body</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>nvarchar</td>
<td STYLE=min-width:50px>-1</td>
<td STYLE=min-width:50px>NULL</td>
</tr>
<tr>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>1</td>
<td STYLE=min-width:50px>DisplayOrder</td>
<td STYLE=min-width:50px>0</td>
<td STYLE=min-width:50px>int</td>
<td STYLE=min-width:50px>NULL</td>
<td STYLE=min-width:50px>10</td>
</tr>
</table> |
如果需要主键和类型，此查询可能有用：
1
2
3
4
5
6
7
8
9
10
11
12
| SELECT L.TABLE_SCHEMA, L.TABLE_NAME, L.COLUMN_NAME, R.TypeName
FROM(
SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
)L
LEFT JOIN (
SELECT
OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,t.name AS TypeName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
)R ON L.COLUMN_NAME = R.ColumnName AND L.TABLE_NAME = R.TableName |
我发现这很有用，它给出了一个表列表，并用逗号分隔了各列，然后又用逗号分隔了其中的几列是主键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| SELECT T.TABLE_SCHEMA, T.TABLE_NAME,
STUFF((
SELECT ', ' + C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
FOR XML PATH ('')
), 1, 2, '') AS COLUMNS,
STUFF((
SELECT ', ' + C.COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON C.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND C.TABLE_NAME = TC.TABLE_NAME
WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
FOR XML PATH ('')
), 1, 2, '') AS [KEY]
FROM INFORMATION_SCHEMA.TABLES T
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME |
|
