我有一个SQL Server数据库,我想知道它有什么列和类型。我更喜欢通过查询而不是使用像Enterprise Manager这样的GUI来实现这一点。有办法做到这一点吗?
当前回答
你可以使用sp_columns存储过程:
exec sp_columns MyTable
其他回答
有几个方法可以获取一个表的元数据:
EXEC sp_help tablename
将返回几个结果集,描述表、它的列和约束。
INFORMATION_SCHEMA视图将为您提供所需的信息,但不幸的是,您必须手动查询这些视图并连接它们。
除了上述问题,如果我们在DB中有像db_name.dbo这样的表。Table_name,我们可以使用以下步骤
连接DB 使用db_name; 使用EXEC sp_help,不要忘记将表名设置为'dbo。表名'如果你有dbo作为schema。 Exec sp_help 'dbo.table_name'
这应该有用!
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>';
样例输出
我在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';
选择表格,按Alt+F1,
它将显示关于表的所有信息,如列名,数据类型,键等。
推荐文章
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- SQL在Oracle中连接多行列值的查询
- 修改列,添加默认约束
- 在单个查询中计算空值和非空值
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- SQL to LINQ工具
- 如何从一个查询插入多行使用雄辩/流利
- 如何连接列在Postgres选择?
- 有人可以对SQL查询进行版权保护吗?
- 如何知道MySQL表最近一次更新?
- 如何转储一些SQLite3表的数据?
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?