我有一个SQL Server数据库,我想知道它有什么列和类型。我更喜欢通过查询而不是使用像Enterprise Manager这样的GUI来实现这一点。有办法做到这一点吗?
当前回答
SELECT C.COLUMN_NAME, C.IS_NULLABLE, C.DATA_TYPE, TC.CONSTRAINT_TYPE, C.COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS AS C
FULL JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CC ON C.COLUMN_NAME = CC.COLUMN_NAME
FULL JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON CC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE C.TABLE_NAME = '<Table Name>';
样例输出
其他回答
这些答案的问题在于,你忽略了关键信息。 虽然这是有点混乱,这是一个快速的版本,我提出,以确保它包含相同的信息MySQL描述显示。
Select SC.name AS 'Field', ISC.DATA_TYPE AS 'Type', ISC.CHARACTER_MAXIMUM_LENGTH AS 'Length', SC.IS_NULLABLE AS 'Null', I.is_primary_key AS 'Key', SC.is_identity AS 'Identity'
From sys.columns AS SC
LEFT JOIN sys.index_columns AS IC
ON IC.object_id = OBJECT_ID('dbo.Expenses') AND
IC.column_id = SC.column_id
LEFT JOIN sys.indexes AS I
ON I.object_id = OBJECT_ID('dbo.Expenses') AND
IC.index_id = I.index_id
LEFT JOIN information_schema.columns ISC
ON ISC.TABLE_NAME = 'Expenses'
AND ISC.COLUMN_NAME = SC.name
WHERE SC.object_id = OBJECT_ID('dbo.Expenses')
您可以使用以下方法:sp_help表名
[示例]sp_help客户
或使用快捷键
选择所需的表并按ALT+F1。
示例:客户按“ALT+F1”。
你可以使用sp_columns存储过程:
exec sp_columns MyTable
我喜欢这种格式:
name DataType Collation Constraints PK FK Comment
id int NOT NULL IDENTITY PK Order Line Id
pid int NOT NULL tbl_orders Order Id
itemCode varchar(10) Latin1_General_CI_AS NOT NULL Product Code
所以我用了这个:
DECLARE @tname varchar(100) = 'yourTableName';
SELECT col.name,
CASE typ.name
WHEN 'nvarchar' THEN 'nvarchar('+CAST((col.max_length / 2) as varchar)+')'
WHEN 'varchar' THEN 'varchar('+CAST(col.max_length as varchar)+')'
WHEN 'char' THEN 'char('+CAST(col.max_length as varchar)+')'
WHEN 'nchar' THEN 'nchar('+CAST((col.max_length / 2) as varchar)+')'
WHEN 'binary' THEN 'binary('+CAST(col.max_length as varchar)+')'
WHEN 'varbinary' THEN 'varbinary('+CAST(col.max_length as varchar)+')'
WHEN 'numeric' THEN 'numeric('+CAST(col.precision as varchar)+(CASE WHEN col.scale = 0 THEN '' ELSE ','+CAST(col.scale as varchar) END) +')'
WHEN 'decimal' THEN 'decimal('+CAST(col.precision as varchar)+(CASE WHEN col.scale = 0 THEN '' ELSE ','+CAST(col.scale as varchar) END) +')'
ELSE typ.name
END DataType,
ISNULL(col.collation_name,'') Collation,
CASE WHEN col.is_nullable = 0 THEN 'NOT NULL ' ELSE '' END + CASE WHEN col.is_identity = 1 THEN 'IDENTITY' ELSE '' END Constraints,
ISNULL((SELECT 'PK'
FROM sys.key_constraints kc INNER JOIN
sys.tables tb ON tb.object_id = kc.parent_object_id INNER JOIN
sys.indexes si ON si.name = kc.name INNER JOIN
sys.index_columns sic ON sic.index_id = si.index_id AND sic.object_id = si.object_id
WHERE kc.type = 'PK'
AND tb.name = @tname
AND sic.column_id = col.column_id),'') PK,
ISNULL((SELECT (SELECT name FROM sys.tables st WHERE st.object_id = fkc.referenced_object_id)
FROM sys.foreign_key_columns fkc INNER JOIN
sys.columns c ON c.column_id = fkc.parent_column_id AND fkc.parent_object_id = c.object_id INNER JOIN
sys.tables t ON t.object_id = c.object_id
WHERE t.name = tab.name
AND c.name = col.name),'') FK,
ISNULL((SELECT value
FROM sys.extended_properties
WHERE major_id = tab.object_id
AND minor_id = col.column_id),'') Comment
FROM sys.columns col INNER JOIN
sys.tables tab ON tab.object_id = col.object_id INNER JOIN
sys.types typ ON typ.system_type_id = col.system_type_id
WHERE tab.name = @tname
AND typ.name != 'sysname'
ORDER BY col.column_id;
有几个方法可以获取一个表的元数据:
EXEC sp_help tablename
将返回几个结果集,描述表、它的列和约束。
INFORMATION_SCHEMA视图将为您提供所需的信息,但不幸的是,您必须手动查询这些视图并连接它们。
推荐文章
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- SQL在Oracle中连接多行列值的查询
- 修改列,添加默认约束
- 在单个查询中计算空值和非空值
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- SQL to LINQ工具
- 如何从一个查询插入多行使用雄辩/流利
- 如何连接列在Postgres选择?
- 有人可以对SQL查询进行版权保护吗?
- 如何知道MySQL表最近一次更新?
- 如何转储一些SQLite3表的数据?
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?