Technical Article

DescribeTable

,

Script displays fieldname, datatype and size.

CREATE PROCEDURE sp_DescribeTable @table varchar(64)
AS

create table #colDesc
      (
      col_name         nvarchar(128)   COLLATE database_default NULL,
      col_id           int                          NULL,
      col_typename     nvarchar(128)   COLLATE database_default NULL,
      col_len          int                          NULL,
      col_prec         int                          NULL,
      col_scale        int                          NULL,
      col_numtype      nvarchar(50)                 NULL,  /* For DaVinci to get sp_help-type filtering of prec/scale */      col_null         bit                          NULL,  /* status & 8 */      col_identity     bit                          NULL,  /* status & 128 */      col_defname      nvarchar(257)  COLLATE database_default NULL,      /* fully-qual'd default name, or NULL */      col_rulname      nvarchar(257)  COLLATE database_default NULL,      /* fully-qual'd rule name, or NULL */      col_basetypename nvarchar(128)   COLLATE database_default NULL,
      col_flags        int                          NULL,      /* COL_* bits */      col_seed         nvarchar (40)      COLLATE database_default NULL,
      col_increment    nvarchar (40)      COLLATE database_default NULL,
      col_dridefname   nvarchar(128)   COLLATE database_default NULL,      /* DRI DEFAULT name */      col_dridefid     int                          NULL,      /* remember the DRI DEFAULT id in syscomments, so we can retrieve it later */      col_iscomputed   int                          NULL,
      col_objectid     int                          NULL,  /* column object id, need it to get computed text from syscomments */      col_NotForRepl   bit                          NULL,  /* Not For Replication setting */      col_fulltext     bit                          NULL,  /* FullTextIndex setting */      col_AnsiPad      bit                          NULL,      /* Ansi_Padding setting */      col_DOwner       nvarchar(128)   COLLATE database_default NULL,      /* non-DRI DEFAULT owner, or NULL */      col_DName        nvarchar(128)   COLLATE database_default NULL,      /* non-DRI DEFAULT name, or NULL */      col_ROwner       nvarchar(128)   COLLATE database_default NULL,      /* non-DRI RULE owner, or NULL */      col_RName        nvarchar(128)   COLLATE database_default NULL,      /* non-DRI RULE name, or NULL */      col_collation    nvarchar(128)   COLLATE database_default NULL,      /* column level collation, valid for string columns only */      col_isindexable  int,
      col_language     int
      )
  
INSERT INTO #colDesc exec sp_MShelpcolumns @table

exec ('Select Col_Name As Name, Col_Typename as DataType, Col_Len as Length From #colDesc Order By Col_Id')

Rate

Share

Share

Rate