获取SqlServer2005表结构可以通过查询系统表和视图来实现,下面是详细的攻略:
查询主键
使用以下语句查询指定表的主键名称:
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = '表名' AND CONSTRAINT_NAME LIKE 'PK_%';
使用以下语句查询指定表的主键字段:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = '表名' AND CONSTRAINT_NAME LIKE 'PK_%';
查询外键
使用以下语句查询指定表的外键名称:
SELECT ForeignKey.name AS FKName
FROM sys.foreign_keys ForeignKey
WHERE ForeignKey.parent_object_id = OBJECT_ID('表名');
使用以下语句查询指定表的外键字段:
SELECT
parent_cols.name AS ParentColumnName,
referenced_cols.name AS ReferencedColumnName
FROM
sys.foreign_key_columns fkc
INNER JOIN
sys.columns parent_cols ON fkc.parent_column_id = parent_cols.column_id AND fkc.parent_object_id = parent_cols.object_id
INNER JOIN
sys.columns referenced_cols ON fkc.referenced_column_id = referenced_cols.column_id AND fkc.referenced_object_id = referenced_cols.object_id
WHERE
parent_cols.object_id = OBJECT_ID('表名');
查询递增列
使用以下语句查询指定表的递增列:
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
[TABLE_NAME] = '表名' AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1;
查询描述
使用以下语句查询指定表的描述:
SELECT [value] FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', '表名', 'column', NULL)
示例说明
- 查询表Test的主键和主键字段
首先,我们使用以下语句查询Test表的主键名称:
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'Test' AND CONSTRAINT_NAME LIKE 'PK_%';
运行结果为:
PK_Test_Id
接下来,我们使用以下语句查询Test表的主键字段:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'Test' AND CONSTRAINT_NAME LIKE 'PK_%';
运行结果为:
Id
- 查询表Employee的递增列和描述
首先,我们使用以下语句查询Employee表的递增列:
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
[TABLE_NAME] = 'Employee' AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1;
运行结果为:
Id
接下来,我们使用以下语句查询Employee表的描述:
SELECT [value] FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'Employee', 'column', NULL)
运行结果为:
Employee table
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:如何获取SqlServer2005表结构(字段,主键,外键,递增,描述) - Python技术站