Quel est l'équivalent de "décrire la table" dans SQL Server?

Vous pouvez utiliser le sp_columns procédure stockée:

exec sp_columns MyTable

Juste au cas où vous ne souhaitez pas utiliser la procédure stockée, voici une requête simple version

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

Utiliser cette Requête

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

Outre les moyens indiqués dans d'autres réponses, vous pouvez utiliser

SELECT TOP 0 * FROM table_name

Cela vous donnera le nom de chaque colonne avec l'absence de résultats en eux, et se termine presque instantanément avec un minimum de frais généraux.

J'ai écrit un sql*plus DESC(RIBE) comme choisir (affiche la colonne des commentaires, trop de) en t-sql:

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

Mentionné ci-dessus, sélectionnez peut être utilisé dans un système marqué procédure stockée et il peut être appelé à partir d'une base de données de votre instance sur une manière simple:

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

Pour exécuter la procédure de type:

EXEC sp_desc 'YourDB.YourSchema.YourTable';

Si vous souhaitez obtenir une description d'un objet de la base de données actuelle (et schéma) de type simple:

EXEC sp_desc 'YourTable';

Comme sp_desc est un système marqué procédure, vous pouvez même laisser la commande exec, trop (pas recommandé de toute façon):

sp_desc 'YourTable';

C'est le code que j'utilise dans le EntityFramework Reverse POCO Generator (disponible ici)

Table 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')

Clé étrangère 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

Propriétés Étendues:

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

SQL Server équivalent à Oracle describe commande est stockée proc sp_help

La describe commande vous donne les informations sur les noms de colonne, le type, la longueur, etc.

Dans SQL Server, disons que vous voulez pour décrire une table "matable" dans le schéma "monschema in de la base de données "mydb", vous pouvez effectuer les opérations suivantes:

USE mydb;
exec sp_help 'myschema.mytable';

utilisation

SELECT COL_LENGTH('tablename', 'colname')

Aucune autre solution a fonctionné pour moi.