我有一个SQL Server数据库,我想知道它有什么列和类型。我更喜欢通过查询而不是使用像Enterprise Manager这样的GUI来实现这一点。有办法做到这一点吗?
当前回答
这是我在EntityFramework反向POCO生成器中使用的代码(可在这里获得)
表的SQL:
SELECT c.TABLE_SCHEMA AS SchemaName,
c.TABLE_NAME AS TableName,
t.TABLE_TYPE AS TableType,
c.ORDINAL_POSITION AS Ordinal,
c.COLUMN_NAME AS ColumnName,
CAST(CASE WHEN IS_NULLABLE = 'YES' THEN 1
ELSE 0
END AS BIT) AS IsNullable,
DATA_TYPE AS TypeName,
ISNULL(CHARACTER_MAXIMUM_LENGTH, 0) AS [MaxLength],
CAST(ISNULL(NUMERIC_PRECISION, 0) AS INT) AS [Precision],
ISNULL(COLUMN_DEFAULT, '') AS [Default],
CAST(ISNULL(DATETIME_PRECISION, 0) AS INT) AS DateTimePrecision,
ISNULL(NUMERIC_SCALE, 0) AS Scale,
CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity,
CAST(CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 1
WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') = 1 THEN 1
WHEN DATA_TYPE = 'TIMESTAMP' THEN 1
ELSE 0
END AS BIT) AS IsStoreGenerated,
CAST(CASE WHEN pk.ORDINAL_POSITION IS NULL THEN 0
ELSE 1
END AS BIT) AS PrimaryKey,
ISNULL(pk.ORDINAL_POSITION, 0) PrimaryKeyOrdinal,
CAST(CASE WHEN fk.COLUMN_NAME IS NULL THEN 0
ELSE 1
END AS BIT) AS IsForeignKey
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN (SELECT u.TABLE_SCHEMA,
u.TABLE_NAME,
u.COLUMN_NAME,
u.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
AND u.TABLE_NAME = tc.TABLE_NAME
AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') pk
ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.COLUMN_NAME = pk.COLUMN_NAME
LEFT OUTER JOIN (SELECT DISTINCT
u.TABLE_SCHEMA,
u.TABLE_NAME,
u.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON u.TABLE_SCHEMA = tc.CONSTRAINT_SCHEMA
AND u.TABLE_NAME = tc.TABLE_NAME
AND u.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY') fk
ON c.TABLE_SCHEMA = fk.TABLE_SCHEMA
AND c.TABLE_NAME = fk.TABLE_NAME
AND c.COLUMN_NAME = fk.COLUMN_NAME
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.TABLE_NAME NOT IN ('EdmMetadata', '__MigrationHistory')
外键SQL:
SELECT FK.name AS FK_Table,
FkCol.name AS FK_Column,
PK.name AS PK_Table,
PkCol.name AS PK_Column,
OBJECT_NAME(f.object_id) AS Constraint_Name,
SCHEMA_NAME(FK.schema_id) AS fkSchema,
SCHEMA_NAME(PK.schema_id) AS pkSchema,
PkCol.name AS primarykey,
k.constraint_column_id AS ORDINAL_POSITION
FROM sys.objects AS PK
INNER JOIN sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS k
ON k.constraint_object_id = f.object_id
INNER JOIN sys.indexes AS i
ON f.referenced_object_id = i.object_id
AND f.key_index_id = i.index_id
ON PK.object_id = f.referenced_object_id
INNER JOIN sys.objects AS FK
ON f.parent_object_id = FK.object_id
INNER JOIN sys.columns AS PkCol
ON f.referenced_object_id = PkCol.object_id
AND k.referenced_column_id = PkCol.column_id
INNER JOIN sys.columns AS FkCol
ON f.parent_object_id = FkCol.object_id
AND k.parent_column_id = FkCol.column_id
ORDER BY FK_Table, FK_Column
扩展属性:
SELECT s.name AS [schema],
t.name AS [table],
c.name AS [column],
value AS [property]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t
ON ep.major_id = t.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
INNER JOIN sys.columns AS c
ON ep.major_id = c.object_id
AND ep.minor_id = c.column_id
WHERE class = 1
ORDER BY t.name
其他回答
我喜欢这种格式:
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;
使用此查询
Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'
你可以使用sp_columns存储过程:
exec sp_columns MyTable
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>';
样例输出
首先连接到你的数据库,
使用DB_name
然后
exec sp_help '生产。Et_Issue”
这里的'production'是模式名。如果你没有图式, 您可以简单地写入sp_help table_name
推荐文章
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- SQL在Oracle中连接多行列值的查询
- 修改列,添加默认约束
- 在单个查询中计算空值和非空值
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- SQL to LINQ工具
- 如何从一个查询插入多行使用雄辩/流利
- 如何连接列在Postgres选择?
- 有人可以对SQL查询进行版权保护吗?
- 如何知道MySQL表最近一次更新?
- 如何转储一些SQLite3表的数据?
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?