SQL Server 中“描述表”的等价物是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/319354/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the equivalent of 'describe table' in SQL Server?
提问by Vincent Ramdhanie
I have a SQL Server database and I want to know what columns and types it has. I'd prefer to do this through a query rather than using a GUI like Enterprise Manager. Is there a way to do this?
我有一个 SQL Server 数据库，我想知道它有哪些列和类型。我更喜欢通过查询而不是使用像企业管理器这样的 GUI 来做到这一点。有没有办法做到这一点？
回答by Vincent Ramdhanie
回答by Brannon
There are a few methods to get metadata about a table:
有几种方法可以获取有关表的元数据：
EXEC sp_help tablename
Will return several result sets, describing the table, it's columns and constraints.
将返回几个结果集，描述表、它的列和约束。
The INFORMATION_SCHEMAviews will give you the information you want, though unfortunately you have to query the views and join them manually.
该INFORMATION_SCHEMA意见会给你你想要的信息，但不幸的是你要查询的意见，并手动将其加入。
回答by Salamander2007
Just in case you don't want to use stored proc, here's a simple query version
以防万一你不想使用存储过程，这里有一个简单的查询版本
select *
from information_schema.columns
where table_name = 'aspnet_Membership'
order by ordinal_position
回答by Viranja kaushalya
You can use following
您可以使用以下
sp_help tablename
sp_help tablename
Example: sp_help Customer
示例：sp_help 客户
OR Use Shortcut
或使用快捷方式
- select table press Alt+F1
- 选择表格按 Alt+F1
Example: Customer Press Alt+F1
示例：客户按 Alt+F1
回答by sukhi
Use this Query
使用此查询
Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'
回答by kingfrito_5005
In addition to the ways shown in other answers, you can use
除了其他答案中显示的方式外，您还可以使用
SELECT TOP 0 * FROM table_name
This will give you the name of each column with no results in them, and completes almost instantly with minimal overhead.
这将为您提供每列的名称，其中没有结果，并且几乎立即以最小的开销完成。
回答by Shwetank Suthar
Just select table and press Alt+F1,
只需选择表格并按Alt+ F1,
it will show all the information about table like Column name, datatype, keys etc.
它将显示有关表的所有信息，如列名、数据类型、键等。
回答by Abhijeet
Please use the following sql query; this worked for my case.
请使用以下sql查询；这适用于我的情况。
select * FROM INFORMATION_SCHEMA.Columns where table_name = 'tablename';
回答by Zsolt Hidasi
I wrote an sql*plus DESC(RIBE) like select (displays the column comments, too) in t-sql:
我在 t-sql 中编写了一个 sql*plus 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];
The above mentioned select can be used in a system marked stored procedure and it can be called from any database of your instance on a simple way:
上面提到的 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
To execute the procedure type:
执行程序类型：
EXEC sp_desc 'YourDB.YourSchema.YourTable';
If you want to get a description an object of the current database (and schema) simple type:
如果您想获取当前数据库（和模式）简单类型的对象的描述：
EXEC sp_desc 'YourTable';
As sp_desc is a system marked procedure, you can even leave the exec command, too (not recommended anyway):
由于 sp_desc 是系统标记的过程，您甚至可以离开 exec 命令（无论如何不推荐）：
sp_desc 'YourTable';
回答by VHS
The SQL Server equivalent to Oracle's describecommand is the stored proc sp_help
相当于 Oracle 的describe命令的 SQL Server是存储过程sp_help
The describecommand gives you the information about the column names, types, length, etc.
该describe命令为您提供有关列名称、类型、长度等的信息。
In SQL Server, let's say you want to describe a table 'mytable' in schema 'myschema' in the database 'mydb', you can do following:
在 SQL Server 中，假设您要在数据库 'mydb' 中的架构 'myschema' 中描述一个表 'mytable'，您可以执行以下操作：
USE mydb;
exec sp_help 'myschema.mytable';
