sp_help - sp_columns sql server

¿Cuál es el equivalente de ''describir tabla'' en SQL Server?

Question

Tengo una base de datos de SQL Server y quiero saber qué columnas y tipos tiene. Preferiría hacer esto a través de una consulta en lugar de usar una GUI como Enterprise Manager. ¿Hay alguna forma de hacer esto?

Answer 1

Además de las formas mostradas en otras respuestas, puede utilizar

SELECT TOP 0 * FROM table_name

Esto le dará el nombre de cada columna sin resultados, y se completa casi instantáneamente con una sobrecarga mínima.

Answer 2

El problema con esas respuestas es que te estás perdiendo la información clave. Aunque esto es un poco complicado, esta es una versión rápida que se me ocurrió para asegurarme de que contenga la misma información que muestra la descripción de MySQL.

Select SC.name AS ''Field'', ISC.DATA_TYPE AS ''Type'', ISC.CHARACTER_MAXIMUM_LENGTH AS ''Length'', SC.IS_NULLABLE AS ''Null'', I.is_primary_key AS ''Key'', SC.is_identity AS ''Identity''
From sys.columns AS SC 
LEFT JOIN sys.index_columns AS IC
ON IC.object_id = OBJECT_ID(''dbo.Expenses'') AND 
IC.column_id = SC.column_id
LEFT JOIN sys.indexes AS I 
ON I.object_id = OBJECT_ID(''dbo.Expenses'') AND 
IC.index_id = I.index_id
LEFT JOIN information_schema.columns ISC
ON ISC.TABLE_NAME = ''Expenses''
AND ISC.COLUMN_NAME = SC.name
WHERE SC.object_id = OBJECT_ID(''dbo.Expenses'')

Answer 3

En caso de que no quiera usar proc almacenado, aquí hay una versión de consulta simple

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

Answer 4

Escribí un sql * más DESC (RIBE) como select (también muestra los comentarios de la columna) 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];

La selección mencionada anteriormente se puede utilizar en un procedimiento almacenado marcado en el sistema y se puede llamar desde cualquier base de datos de su instancia de una manera 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

Para ejecutar el tipo de procedimiento:

EXEC sp_desc ''YourDB.YourSchema.YourTable'';

Si desea obtener una descripción, un objeto de la base de datos actual (y el esquema) de tipo simple:

EXEC sp_desc ''YourTable'';

Como sp_desc es un procedimiento marcado por el sistema, también puede dejar el comando exec (no recomendado de todos modos):

sp_desc ''YourTable'';

Answer 5

Este es el código que uso dentro del EntityFramework Reverse POCO Generator (disponible here )

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

Clave foránea 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

Propiedades extendidas:

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

Answer 6

Existen algunos métodos para obtener metadatos sobre una tabla:

EXEC sp_help @tablename

Devolverá varios conjuntos de resultados, describiendo la tabla, sus columnas y restricciones.

Las vistas INFORMATION_SCHEMA le darán la información que desea, aunque desafortunadamente tiene que consultar las vistas y unirlas manualmente.

Answer 7

Por favor, utilice la siguiente consulta SQL; esto funcionó para mi caso.

select * FROM   INFORMATION_SCHEMA.Columns where table_name = ''tablename'';

Answer 8

Puede utilizar el procedimiento almacenado sp_columns :

exec sp_columns MyTable

Answer 9

Puedes usar el sp_help ''TableName''

Answer 10

Puedes usar los siguientes

sp_help tablename

Ejemplo: cliente sp_help

O utilice el atajo

• seleccionar tabla presionar Alt + F1

Ejemplo: Cliente Presione Alt + F1

Answer 11

Solo selecciona tabla y presiona Alt + F1 ,

Mostrará toda la información sobre la tabla como el nombre de la columna, el tipo de datos, las claves, etc.

Answer 12

Usar esta consulta

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

Answer 13

utilizar

SELECT COL_LENGTH(''tablename'', ''colname'')

Ninguna otra solución funcionó para mí.

Answer 14

CREATE PROCEDURE [dbo].[describe] 
( 
@SearchStr nvarchar(max) 
) 
AS 
BEGIN 
SELECT  
    CONCAT([COLUMN_NAME],'' '',[DATA_TYPE],'' '',[CHARACTER_MAXIMUM_LENGTH],'' '', 
    (SELECT CASE [IS_NULLABLE] WHEN ''NO'' THEN ''NOT NULL'' ELSE ''NULL'' END),
    (SELECT CASE WHEN [COLUMN_DEFAULT] IS NULL THEN '''' ELSE CONCAT('' DEFAULT '',[COLUMN_DEFAULT]) END)
    ) AS DESCRIPTION
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE @SearchStr
END