'describe table'SQL Server?

小开

有几个方法可以获取一个表的元数据:

EXEC sp_help tablename

将返回几个结果集，描述表、它的列和约束。

INFORMATION_SCHEMA视图会给你想要的信息，不过不幸的是，你必须手动查询这些视图并连接它们。

小开

你可以使用sp_columns存储过程:

exec sp_columns MyTable

小开

以防您不想使用stored proc，这里有一个简单的查询版本

select *
from information_schema.columns
where table_name = 'aspnet_Membership'
order by ordinal_position

小开

这些答案的问题在于你忽略了关键信息。虽然这是有点混乱，这是一个快速的版本，我提出，以确保它包含相同的信息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')

小开

我在t-sql中写了一个sql* + DESC(RIBE)，比如select(显示列注释):

USE YourDB
GO


DECLARE @objectName NVARCHAR(128) = 'YourTable';


SELECT
a.[NAME]
,a.[TYPE]
,a.[CHARSET]
,a.[COLLATION]
,a.[NULLABLE]
,a.[DEFAULT]
,b.[COMMENTS]
-- ,a.[ORDINAL_POSITION]
FROM
(
SELECT
COLUMN_NAME                                     AS [NAME]
,CASE DATA_TYPE
WHEN 'char'       THEN DATA_TYPE  + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
WHEN 'numeric'    THEN DATA_TYPE  + '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
WHEN 'nvarchar'   THEN DATA_TYPE  + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
WHEN 'varbinary'  THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
WHEN 'varchar'    THEN DATA_TYPE   + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
ELSE DATA_TYPE
END                                             AS [TYPE]
,CHARACTER_SET_NAME                              AS [CHARSET]
,COLLATION_NAME                                  AS [COLLATION]
,IS_NULLABLE                                     AS [NULLABLE]
,COLUMN_DEFAULT                                  AS [DEFAULT]
,ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @objectName
) a
FULL JOIN
(
SELECT
CAST(value AS NVARCHAR)                        AS [COMMENTS]
,CAST(objname AS NVARCHAR)                      AS [NAME]
FROM
::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @objectName, 'column', default)
) b
ON a.NAME COLLATE YourCollation = b.NAME COLLATE YourCollation
ORDER BY
a.[ORDINAL_POSITION];

上面提到的select可以在一个系统标记的存储过程中使用，并且可以从你实例的任何数据库中以简单的方式调用它:

USE master;
GO


IF OBJECT_ID('sp_desc', 'P') IS NOT NULL
DROP PROCEDURE sp_desc
GO


CREATE PROCEDURE sp_desc (
@tableName  nvarchar(128)
) AS
BEGIN
DECLARE @dbName       sysname;
DECLARE @schemaName   sysname;
DECLARE @objectName   sysname;
DECLARE @objectID     int;
DECLARE @tmpTableName varchar(100);
DECLARE @sqlCmd       nvarchar(4000);


SELECT @dbName = PARSENAME(@tableName, 3);
IF @dbName IS NULL SELECT @dbName = DB_NAME();


SELECT @schemaName = PARSENAME(@tableName, 2);
IF @schemaName IS NULL SELECT @schemaName = SCHEMA_NAME();


SELECT @objectName = PARSENAME(@tableName, 1);
IF @objectName IS NULL
BEGIN
PRINT 'Object is missing from your function call!';
RETURN;
END;


SELECT @objectID = OBJECT_ID(@dbName + '.' + @schemaName + '.' + @objectName);
IF @objectID IS NULL
BEGIN
PRINT 'Object [' + @dbName + '].[' + @schemaName + '].[' + @objectName + '] does not exist!';
RETURN;
END;


SELECT @tmpTableName = '#tmp_DESC_' + CAST(@@SPID AS VARCHAR) + REPLACE(REPLACE(REPLACE(REPLACE(CAST(CONVERT(CHAR, GETDATE(), 121) AS VARCHAR), '-', ''), ' ', ''), ':', ''), '.', '');
--PRINT @tmpTableName;
SET @sqlCmd = '
USE ' + @dbName + '
CREATE TABLE ' + @tmpTableName + ' (
[NAME]              nvarchar(128) NOT NULL
,[TYPE]              varchar(50)
,[CHARSET]           varchar(50)
,[COLLATION]         varchar(50)
,[NULLABLE]          varchar(3)
,[DEFAULT]           nvarchar(4000)
,[COMMENTS]          nvarchar(3750));


INSERT INTO ' + @tmpTableName + '
SELECT
a.[NAME]
,a.[TYPE]
,a.[CHARSET]
,a.[COLLATION]
,a.[NULLABLE]
,a.[DEFAULT]
,b.[COMMENTS]
FROM
(
SELECT
COLUMN_NAME                                     AS [NAME]
,CASE DATA_TYPE
WHEN ''char''      THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''
WHEN ''numeric''   THEN DATA_TYPE + ''('' + CAST(NUMERIC_PRECISION AS VARCHAR) + '', '' + CAST(NUMERIC_SCALE AS VARCHAR) + '')''
WHEN ''nvarchar''  THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''
WHEN ''varbinary'' THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''
WHEN ''varchar''   THEN DATA_TYPE + ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + '')''
ELSE DATA_TYPE
END                                             AS [TYPE]
,CHARACTER_SET_NAME                              AS [CHARSET]
,COLLATION_NAME                                  AS [COLLATION]
,IS_NULLABLE                                     AS [NULLABLE]
,COLUMN_DEFAULT                                  AS [DEFAULT]
,ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = ''' + @objectName + '''
) a
FULL JOIN
(
SELECT
CAST(value AS NVARCHAR)                        AS [COMMENTS]
,CAST(objname AS NVARCHAR)                      AS [NAME]
FROM
::fn_listextendedproperty (''MS_Description'', ''user'', ''' + @schemaName + ''', ''table'', ''' + @objectName + ''', ''column'', default)
) b
ON a.NAME COLLATE Hungarian_CI_AS = b.NAME COLLATE Hungarian_CI_AS
ORDER BY
a.[ORDINAL_POSITION];


SELECT * FROM ' + @tmpTableName + ';'


--PRINT @sqlCmd;


EXEC sp_executesql @sqlCmd;
RETURN;
END;
GO


EXEC sys.sp_MS_marksystemobject sp_desc
GO

执行过程类型:

EXEC sp_desc 'YourDB.YourSchema.YourTable';

如果你想获取当前数据库(和模式)简单类型对象的描述:

EXEC sp_desc 'YourTable';

因为sp_desc是一个系统标记的过程，你甚至可以留下exec命令(无论如何不推荐):

sp_desc 'YourTable';

小开

使用此查询

Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'

小开

你可以使用sp_help 'TableName'

小开

你可以使用以下语句:sp_help tablename

例如:sp_help Customer

或使用快捷键

Select，并按ALT+F1。

示例:客户按ALT+F1。

小开

这是我在EntityFramework Reverse POCO Generator中使用的代码(可用在这里)

< p >表的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

< p >扩展属性:

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

小开

CREATE PROCEDURE [dbo].[describe]
(
@SearchStr nvarchar(max)
)
AS
BEGIN
SELECT
CONCAT([COLUMN_NAME],' ',[DATA_TYPE],' ',[CHARACTER_MAXIMUM_LENGTH],' ',
(SELECT CASE [IS_NULLABLE] WHEN 'NO' THEN 'NOT NULL' ELSE 'NULL' END),
(SELECT CASE WHEN [COLUMN_DEFAULT] IS NULL THEN '' ELSE CONCAT(' DEFAULT ',[COLUMN_DEFAULT]) END)
) AS DESCRIPTION
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE @SearchStr
END

小开

只需要选择表格并按Alt+F1，

它将显示关于表的所有信息，如列名，数据类型，键等。

小开

请使用以下sql查询;这对我的案子很管用。

select * FROM   INFORMATION_SCHEMA.Columns where table_name = 'tablename';

小开

除了在其他答案中显示的方法，你还可以使用

SELECT TOP 0 * FROM table_name

这将为您提供每个列的名称，其中没有结果，并且几乎立即以最小的开销完成。

小开

使用

SELECT COL_LENGTH('tablename', 'colname')

其他的解决方法对我都不起作用。

小开

与Oracle的describe命令等价的SQL Server是存储过程sp_help

describe命令提供了列名、类型、长度等信息。

在SQL Server中，假设你想在数据库'mydb'中的模式'myschema'中描述一个表'mytable'，你可以这样做:

USE mydb;
exec sp_help 'myschema.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;

小开

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>';

样本输出 .

小开

如果你正在使用Brent Ozar团队的FirstResponderKit，你也可以运行这个查询:

exec sp_blitzindex @tablename='MyTable'

它将返回关于表的所有信息:

索引及其使用统计信息(读、写、锁等)，空间 Used和其他
缺失索引
列
外键

当然，它不是一个系统，也不像sp_help或sp_columns那样通用，但它返回关于你的表的所有可能的信息，我认为值得在你的环境中创建它并在这里提到它。

小开

只需双击表名并按Alt+F1

小开

除了上述问题，如果我们在DB中有像db_name.dbo.table_name这样的表，我们可以使用以下步骤

连接DB

USE db_name;
使用EXEC sp_help，如果你有dbo作为模式，不要忘记把表名作为'dbo.tablename'。

exec sp_help 'dbo.table_name'

这应该有用!

小开

我试过了，对我很有效

exec sp_help TABLE_NAME

小开

下面的查询将提供类似于python Pandas库中的info()函数的输出。

USE [Database_Name]


IF OBJECT_ID('tempdo.dob.#primary_key', 'U') IS NOT NULL DROP TABLE #primary_key


SELECT
CONS_T.TABLE_CATALOG,
CONS_T.TABLE_SCHEMA,
CONS_T.TABLE_NAME,
CONS_C.COLUMN_NAME,
CONS_T.CONSTRAINT_TYPE,
CONS_T.CONSTRAINT_NAME
INTO  #primary_key
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CONS_T
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CONS_C ON CONS_C.CONSTRAINT_NAME= CONS_T.CONSTRAINT_NAME




SELECT
SMA.name AS [Schema Name],
ST.name AS [Table Name],
SC.column_id AS [Column Order],
SC.name AS [Column Name],
PKT.CONSTRAINT_TYPE,
PKT.CONSTRAINT_NAME,
SC.system_type_id,
STP.name AS [Data Type],
SC.max_length,
SC.precision,
SC.scale,
SC.is_nullable,
SC.is_masked
FROM sys.tables  AS ST
JOIN sys.schemas AS SMA ON SMA.schema_id = ST.schema_id
JOIN sys.columns AS SC ON SC.object_id = ST.object_id
JOIN sys.types AS STP ON STP.system_type_id = SC.system_type_id
LEFT JOIN #primary_key AS PKT ON PKT.TABLE_SCHEMA = SMA.name
AND PKT.TABLE_NAME = ST.name
AND PKT.COLUMN_NAME = SC.name
ORDER BY ST.name ASC, SMA.name ASC

小开

试一试:

EXEC [ServerName].[DatabaseName].dbo.sp_columns 'TableName'

你可以得到一些表结构的信息，比如:

Table_qualifier, table_owner, table_name, column_name, data_type, type_name…

小开

首先连接到你的数据库，

使用DB_name

然后

exec sp_help '生产。Et_Issue”

这里的'production'是模式名。如果你没有图式，你可以简单地写sp_help table_name