DESC
命令一样。我有一个名为
[EX].[dbo].[EMP_MAST]
的表需要描述,但是它不起作用。错误信息如下:
对象“EMP_MAST”不存在于数据库“master”中,或者对于此操作无效。
DESC
命令一样。[EX].[dbo].[EMP_MAST]
的表需要描述,但是它不起作用。对象“EMP_MAST”不存在于数据库“master”中,或者对于此操作无效。
根据此文档:
DESC MY_TABLE
等同于
SELECT column_name "名称", nullable "是否为空?", concat(concat(concat(data_type,'('),data_length),')') "类型" FROM user_tab_columns WHERE table_name='TABLE_NAME_TO_DESCRIBE';
我已经为您粗略地将其翻译为SQL Server的等效语句 - 确保在EX
数据库上运行它。
SELECT column_name AS [name],
IS_NULLABLE AS [null?],
DATA_TYPE + COALESCE('(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1
THEN 'Max'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
END + ')', '') AS [type]
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'EMP_MAST'
在我看来,sp_help内置过程是SQL Server最接近Oracle DESC函数的东西
sp_help MyTable
sp_help "[SchemaName].[TableName]"
sp_help "[InstanceName].[SchemaName].[TableName]"
如果您需要进一步限定表名
可能这个会有所帮助:
Use MyTest
Go
select * from information_schema.COLUMNS where TABLE_NAME='employee'
{ where: MyTest= 数据库名 Employee= 表名 } --可选条件
CREATE FUNCTION dbo.describe (@TABLENAME varchar(50))
returns table
as
RETURN
(
SELECT TOP 1000 column_name AS [ColumnName],
IS_NULLABLE AS [IsNullable],
DATA_TYPE + '(' + CASE
WHEN DATA_TYPE = 'varchar' or DATA_TYPE = 'char' THEN
CASE
WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'Max'
ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
END
WHEN DATA_TYPE = 'decimal' or DATA_TYPE = 'numeric' THEN
Cast(NUMERIC_PRECISION AS VARCHAR(5))+', '+Cast(NUMERIC_SCALE AS VARCHAR(5))
WHEN DATA_TYPE = 'bigint' or DATA_TYPE = 'int' THEN
Cast(NUMERIC_PRECISION AS VARCHAR(5))
ELSE ''
END + ')' AS [DataType]
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = @TABLENAME
order by ordinal_Position
);
GO
create table dbo.yourtable
(columna bigint,
columnb int,
columnc datetime,
columnd varchar(100),
columne char(10),
columnf bit,
columng numeric(10,2),
columnh decimal(10,2)
)
select * from describe ('yourtable')
它返回以下内容
列名称 是否可空 数据类型
columna 否 bigint(19)
columnb 否 int(10)
columnc 否 datetime()
columnd 否 varchar(100)
columne 否 char(10)
columnf 否 bit()
columng 否 numeric(10, 2)
columnh 否 decimal(10, 2)
希望这能帮助有需要的人。
只需输入以下行。
exec sp_help [table_name]
SELECT column_name AS [Name],
IS_NULLABLE AS [Null?],
DATA_TYPE + CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
WHEN CHARACTER_MAXIMUM_LENGTH > 99999 THEN ''
ELSE '(' + Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) + ')'
END AS [Type]
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'table_name'
主要更改: