我有一个SQL Server数据库,想知道它有哪些列和数据类型。我更倾向于通过查询来完成这个任务而不是使用Enterprise Manager等GUI工具。有没有一种方法可以做到这一点?
我有一个SQL Server数据库,想知道它有哪些列和数据类型。我更倾向于通过查询来完成这个任务而不是使用Enterprise Manager等GUI工具。有没有一种方法可以做到这一点?
你可以使用 sp_columns 存储过程:
exec sp_columns MyTable
select
语句的解决方案更加适合sqlcmd用户。 - ctpenrose获取表的元数据有几种方法:
EXEC sp_help tablename
将返回多个结果集,描述表格、其列和约束。
INFORMATION_SCHEMA
视图可以提供所需信息,但不幸的是必须手动查询视图并进行联接。
@
是不正确的语法。 - pcnate@tablename
是示例中的一个变量。该存储过程可以双向使用,无论是字符串还是“普通”的表名(exec sp_help Employees
或exec sp_help 'Employees'
)。 - KekuSemauexecute sp_help 'db.sch.your_table'
- ColinMac如果您不想使用存储过程,这里有一个简单的查询版本。
select *
from information_schema.columns
where table_name = 'aspnet_Membership'
order by ordinal_position
sp_help tablename
示例:sp_help Customer
或使用快捷键:选择
所需的表格,然后按下ALT+F1
示例:选中Customer表格,按下ALT+F1使用此查询。
Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'
除了其他答案中展示的方法外,您还可以使用
SELECT TOP 0 * FROM table_name
这将为您提供每个没有结果的列的名称,并在极小的开销下几乎立即完成。
select * FROM INFORMATION_SCHEMA.Columns where table_name = 'tablename';
只需选择表格并按下Alt+F1,它会显示有关表格的所有信息,例如列名、数据类型、键等。
SQL Server中与Oracle的describe
命令相当的是存储过程sp_help
describe
命令可以提供关于列名、类型、长度等信息。
在SQL Server中,假设您想要描述数据库“mydb”中模式为“myschema”的表格“mytable”,您可以执行以下操作:
USE mydb;
exec sp_help 'myschema.mytable';
我写了一个类似于t-sql的sql*plus DESC(RIBE)语句(会显示列注释),如下:
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];
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是一个系统标记过的过程,因此您甚至可以省略执行命令(不建议这样做):
sp_desc 'YourTable';