Retrieve column attributes

,

Given database, owner, table, and column names, this procedure returns one from among several column attributes such as type definition, defaults, and other types of constraints.  E.g., to retrieve the type definition of the 'pubs' database's 'author.address':

DECLARE @attrib VARCHAR (500)
      , @msg    VARCHAR (8000)

EXEC p_get_column_attribute 'TYPEDEF'
                          , 'authors'
                          , 'address'
                          , @msg    OUTPUT
                          , @attrib OUTPUT
                          , 'pubs', 'dbo'
PRINT @attrib

(Answer:  'VARCHAR (40)')

CREATE PROCEDURE p_get_column_attribute
                   @p_which_attrib SYSNAME
                 , @p_tablename    SYSNAME
                 , @p_columnname   SYSNAME
                 , @p_out_msg      VARCHAR (8000) OUTPUT
                 , @p_out_attrib   VARCHAR (500)  OUTPUT
                 , @p_dbname       SYSNAME = NULL
                 , @p_owner        SYSNAME = NULL
                 
/*
||======================================================================
|| Date written: 4/8/2002 (Dise)
||
|| Purpose:  To return, in SQL, the requested column attribute of a
||           specified table column.
||
||           Note:  This is a procedure, not a function, because
||           it needs to be database-independent.
||======================================================================
*/AS
  /*
  ||====================================================================
  || Local variable declarations...
  ||====================================================================
  */  DECLARE @retc           INT
        , @TRUE           BIT
        , @FALSE          BIT
        , @msg            VARCHAR (8000)
        , @sqlcmd         VARCHAR (8000)
        , @attrib         VARCHAR (500)
        , @coldef         VARCHAR (60)
        , @consttype      VARCHAR (2)
        , @delim          VARCHAR (2)
        , @length         SMALLINT
        , @prec           SMALLINT
        , @scale          SMALLINT
        , @dbname         SYSNAME
        , @groupname      SYSNAME
        , @owner          SYSNAME
        , @tablename      SYSNAME
        , @columnname     SYSNAME
        , @keyname        SYSNAME
        , @partialname    SYSNAME
        , @q              CHAR (1)
        , @autoval        VARBINARY (255)
        , @seedbin        BINARY (4)
        , @incrbin        BINARY (4)
        , @seed           INT
        , @id             INT
        , @colid          INT
        , @indid          INT
        , @constid        INT
        , @incr           INT
        , @iscomputed     INT
        , @pos            INT
        , @origfillfactor TINYINT

  /*
  ||====================================================================
  || Inititializations...
  ||====================================================================
  */  SELECT @retc   = 0
       , @TRUE   = 1
       , @FALSE  = 0
       , @attrib = ''
       , @q      = CHAR (39)

  /*
  ||====================================================================
  || Validations...
  ||====================================================================
  */  /*
  ||====================================================================
  || Ensure the attribute requested is one that is supported...
  ||====================================================================
  */  IF @p_which_attrib NOT IN ('TYPEDEF'
                           , 'DEFAULT'
                           , 'IDENTITYSTAT'
                           , 'NULLABILITY'
                           , 'PRIMARYKEY'
                           , 'FOREIGNKEY'
                           , 'CHECK'
                           , 'UNIQUE')
--THEN
    BEGIN
      SELECT @msg = ' only attributes supported:'
                  + ' TYPEDEF, DEFAULT, IDENTITYSTAT, NULLABILITY,'
                  + ' PRIMARYKEY, FOREIGNKEY, CHECK, UNIQUE'
      GOTO PRC_ERROR
    END
--END IF
  
  /*
  ||====================================================================
  || Ensure the database exists on the server...
  ||====================================================================
  */  IF @p_dbname IS NULL
--THEN
    SELECT @dbname = DB_NAME ()
  ELSE
    IF EXISTS (SELECT 1
               FROM   master..sysdatabases
               WHERE  name = @p_dbname)
  --THEN
      SELECT @dbname = @p_dbname
    ELSE
      BEGIN
        SELECT @msg = ' Input database name '
                    + @p_dbname
                    + ' not found on server '
                    + @@SERVERNAME
        GOTO PRC_ERROR
      END
  --END IF
--END IF

  /*
  ||====================================================================
  || Ensure the owner exists on the database...
  ||====================================================================
  */  CREATE TABLE #exists
    (existence INT)

  IF ISNULL (@p_owner, 'dbo') = 'dbo'
--THEN
    SELECT @owner = 'dbo'
  ELSE
    BEGIN
      SELECT @sqlcmd =  'SELECT 1'
                     + ' FROM ' + @dbname + '..sysusers'
                     + ' WHERE name = ' + @q + @p_owner + @q
                     
      INSERT INTO #exists
        EXEC (@sqlcmd)

      IF EXISTS (SELECT 1
                 FROM   #exists
                 WHERE  existence = @TRUE)
    --THEN
        SELECT @owner = @p_owner
      ELSE
        BEGIN
          SELECT @msg = ' user ' + @q + @p_owner + @q
                      + ' not found on database ' + @q + @dbname + @q
          GOTO PRC_ERROR
        END
    --END IF
    END
--END IF
  
  /*
  ||====================================================================
  || Ensure the table exists on the database...
  ||====================================================================
  */  TRUNCATE TABLE #exists

  SELECT @sqlcmd =  'SELECT o.id'
                 + ' FROM '   + @dbname + '..sysobjects o'
                 +   ' JOIN ' + @dbname + '..sysusers   u'
                 +     ' ON o.uid = u.uid'
                 + ' WHERE u.name = ' + @q + @owner       + @q
                 +   ' AND o.name = ' + @q + @p_tablename + @q
                 
  INSERT INTO #exists
    EXEC (@sqlcmd)

  IF EXISTS (SELECT 1
             FROM   #exists)
--THEN
    SELECT @id        = existence
         , @tablename = @p_tablename
    FROM  #exists
  ELSE
    BEGIN
      SELECT @msg = ' table ' + @q + @owner   + '.' + @p_tablename + @q
                  + ' not found on database ' + @q  + @dbname      + @q
      GOTO PRC_ERROR
    END
--END IF

  /*
  ||====================================================================
  || Ensure the column exists on the table...
  ||====================================================================
  */  TRUNCATE TABLE #exists

  SELECT @sqlcmd =  'SELECT colid'
                 + ' FROM '   + @dbname + '..syscolumns'
                 + ' WHERE id   = '     + CONVERT (VARCHAR, @id)
                 +   ' AND name = '     + @q + @p_columnname + @q

  INSERT INTO #exists
    EXEC (@sqlcmd)

  IF EXISTS (SELECT 1
             FROM  #exists)
--THEN
    SELECT @columnname = @p_columnname
         , @colid      = existence
    FROM  #exists      
  ELSE
    BEGIN
      SELECT @msg = ' table ' + @q + @owner  + '.' + @p_tablename + @q
                  + ' on database ' + @q + @dbname + @q
                  + ' has no column named ' + @q + @p_columnname + @q
      GOTO PRC_ERROR
    END
--END IF
  
  /*
  ||====================================================================
  || End of validations.  At this point in the logic, we know the
  || database, the table owner, the table, and the column, and we also
  || know that the attribute being requested is supported...
  ||
  || Get the attribute and send it back.  Since this works independently
  || from any database, dynamic SQL must be used to query the system
  || tables of the chosen database, and the results passed back into a
  || temporary table.
  ||
  || So, for each supported attribute, a query is constructed within
  || a string and then executed; the results are scooped into a
  || temporary table and then used by the procedure to format the
  || attribute in SQL.
  ||
  || First one up is the type definition (i.e., VARCHAR, INT, etc.).  If
  || a column is computed, the formula for computing it is returned.
  ||====================================================================
  */  IF @p_which_attrib = 'TYPEDEF'
--THEN
    BEGIN
      CREATE TABLE #typedefs
        (iscomputed INT
       , coldef     VARCHAR (8000)
       , length     INT
       , prec       INT
       , scale      INT)
      
      SELECT @sqlcmd =
           'SELECT c.iscomputed'
        +       ', CASE'
        +          ' WHEN c.iscomputed = 0'
        +          ' THEN UPPER (t.name)'
        +          ' ELSE s.text'
        +        ' END'
        +       ', c.length'
        +       ', c.prec'
        +       ', c.scale'
        + ' FROM   '      + @dbname + '..syscolumns c'
        +   ' LEFT JOIN ' + @dbname + '..systypes   t'
        +     ' ON c.xtype = t.xtype'
        +   ' LEFT JOIN ' + @dbname + '..syscomments s'
        +     ' ON c.id    = s.id'
        +    ' AND c.colid = s.number'
        +    ' AND s.colid = 1'
        + ' WHERE  c.id    = ' + CONVERT (VARCHAR, @id)
        + '   AND  c.colid = ' + CONVERT (VARCHAR, @colid)

      INSERT INTO #typedefs
        EXEC (@sqlcmd)

      SELECT @iscomputed = iscomputed
           , @coldef     = coldef
           , @length     = length
           , @prec       = prec
           , @scale      = scale
      FROM   #typedefs

      IF @iscomputed = @TRUE
    --THEN
        SELECT @attrib = 'AS ' + @coldef
      ELSE
        BEGIN
          IF @coldef LIKE '%NCHAR%'
          OR @coldef LIKE '%NVARCHAR%'
        --THEN
            SELECT @attrib = @coldef
                           + ' ('
                           + CONVERT (VARCHAR, @length / 2)
                           + ')'
          ELSE IF @coldef LIKE '%CHAR%'
               OR @coldef LIKE '%BIN%'
        --THEN
            SELECT @attrib = @coldef
                           + ' ('
                           + CONVERT (VARCHAR, @length)
                           + ')'
          ELSE IF @coldef LIKE '%DEC%'
               OR @coldef LIKE '%NUM%'
        --THEN
            SELECT @attrib = @coldef
                           + ' ('
                           + CONVERT (VARCHAR, @prec)
                           + CASE @scale
                               WHEN 0
                               THEN ''
                               ELSE ', '
                                  + CONVERT (VARCHAR, @scale)
                             END
                           + ')'
          ELSE
            SELECT @attrib = @coldef
        --END IF
        END
    --END IF
    END

  /*
  ||====================================================================
  || If DEFAULT is requested, this returns an empty string if the
  || specified column has no defined default value, but the appropriate
  || SQL if it does.  If a default exists, the table's row in sysobjects
  || will have a child row, also in sysobjects, of xtype = 'D'; also,
  || there will be a related row in sysconstraints where the status
  || columns bitmaps to 5.
  ||====================================================================
  */  ELSE IF @p_which_attrib = 'DEFAULT'
--THEN
    BEGIN
      CREATE TABLE #defaults
        (attrib VARCHAR (8000))

      SELECT @sqlcmd =
           'SELECT m.text'
        + ' FROM   '  + @dbname + '..syscolumns    c'
        +   ' JOIN '  + @dbname + '..sysobjects    o'
        +     ' ON o.parent_obj = ' + CONVERT (VARCHAR, @id)
        +   ' JOIN '  + @dbname + '..sysconstraints x'
        +     ' ON o.id    = x.constid'
        +    ' AND c.id    = x.id'
        +    ' AND c.colid = x.colid'
        +   ' JOIN '  + @dbname + '..syscomments    m'
        +     ' ON x.constid = m.id'
        +   ' WHERE c.id           = ' + CONVERT (VARCHAR, @id)
        +     ' AND c.colid        = ' + CONVERT (VARCHAR, @colid)
        +     ' AND o.xtype        = ' + @q + 'D' + @q
        +     ' AND x.status & 0xF = 5'

        INSERT INTO #defaults
          EXEC (@sqlcmd)

        IF EXISTS (SELECT 1
                   FROM  #defaults)
      --THEN
          SELECT @attrib = 'DEFAULT ' + attrib
          FROM  #defaults
      --END IF
    --END IF
    END
  
  /*
  ||====================================================================
  || If IDENTITYSTAT is requested, this returns an empty string if the
  || specified column does not have the IDENTITY attribute; but if it
  || does, it returns the SQL for IDENTITY, and also the seed/increment
  || if other than the default (1, 1).
  ||
  || This is one of the trickier ones.  The IDENT_SEED and IDENT_INCR
  || system functions would have been useful, except for the fact that
  || they operate only within a given database.  Since this procedure
  || will reside in a different database (Utils) than most of the data,
  || there was a need to get closer to the system metadata than was
  || attractive.
  ||
  || If a column has the IDENTITY attribute, its corresponding 'status'
  || and 'autoval' values in syscolumns tells the story.  The status
  || column will bitmap to x80 for all IDENTITY columns.  Moreover,
  || the seed and increment values can be determined by examining the
  || autoval column.  The seed is contained in the first four bytes on
  || this column, and the increment in the next four bytes, represented
  || in both cases by a hexadecimal value.  However, to complicate
  || matters, the low-order hexadecimal digit is to the left and ascends
  || to the right, contrary to what you might otherwise expect.
  ||
  || The strategy is simply to figure out what the number represented in
  || hex is, and multiply each hex digit by a power of 256 as the
  || high-order digits are encountered one at a time...
  ||====================================================================
  */  ELSE IF @p_which_attrib = 'IDENTITYSTAT'
--THEN
    BEGIN
      CREATE TABLE #idents
        (autoval VARBINARY (255))

      SELECT @sqlcmd =
           'SELECT autoval'
        + ' FROM '  + @dbname + '..syscolumns'
        + ' WHERE id    = '   + CONVERT (VARCHAR, @id)
        +   ' AND colid = '   + CONVERT (VARCHAR, @colid)
        +   ' AND status & 0x80 != 0'

      INSERT INTO #idents
        EXEC (@sqlcmd)

      IF EXISTS (SELECT 1
                 FROM  #idents
                 WHERE autoval IS NOT NULL
                   AND LEN (autoval) >= 12)
    --THEN
        BEGIN
          SELECT @attrib = 'IDENTITY'

          SELECT @autoval = autoval
          FROM  #idents

          SELECT @seedbin = SUBSTRING (@autoval, 9, 4)
               , @incrbin = SUBSTRING (@autoval, 5, 4)

          SELECT @pos  = 0
               , @seed = 0
               , @incr = 0

          WHILE @pos < 4
          BEGIN
            SELECT @pos  = @pos + 1
            SELECT @seed = @seed
                         + CONVERT (INT, SUBSTRING (@seedbin, @pos, 1))
                             * POWER (256, @pos - 1)
                 , @incr = @incr
                         + CONVERT (INT, SUBSTRING (@incrbin, @pos, 1))
                             * POWER (256, @pos - 1)
          END

          IF @seed != 1
          OR @incr != 1
        --THEN
            SELECT @attrib = @attrib
                           + ' ('
                           + CONVERT (VARCHAR, @seed)
                           + ', '
                           + CONVERT (VARCHAR, @incr)
                           + ')'
        --END IF
        END
    --END IF
    END
    
  /*
  ||====================================================================
  || If NULLABILITY is requested, this returns either NULL or NOT NULL
  || for any column...
  ||====================================================================
  */  ELSE IF @p_which_attrib = 'NULLABILITY'
--THEN
    BEGIN
      CREATE TABLE #nulls
        (isnullable INT)

      SELECT @sqlcmd =
           'SELECT isnullable'
        + ' FROM '  + @dbname + '..syscolumns'
        + ' WHERE id    = '   + CONVERT (VARCHAR, @id)
        +   ' AND colid = '   + CONVERT (VARCHAR, @colid)

      INSERT INTO #nulls
        EXEC (@sqlcmd)

      IF EXISTS (SELECT 1
                 FROM   #nulls
                 WHERE  isnullable = @TRUE)
    --THEN
        SELECT @attrib = 'NULL'
      ELSE
        SELECT @attrib = 'NOT NULL'
    --END IF
    END

  /*
  ||====================================================================
  || This procedure only returns column-level constraints.  Any
  || PRIMARY KEY or UNIQUE constraint that spans more than one column
  || is a table-level constraint and must be dealt with in another
  || procedure.
  ||
  || If PRIMARYKEY is requested and the column under examination is not
  || a primary key, a zero-length string is returned.  If it is a
  || primary key, however, the text that defines it in SQL is sent
  || back...
  ||
  || If UNIQUE is requested and the column under examination is not the
  || owner of a unique constraint, a zero-length string is returned.
  || Otherwise, the text that defines it in SQL is sent back...
  ||====================================================================
  */  ELSE IF @p_which_attrib IN ('PRIMARYKEY', 'UNIQUE')
--THEN
    BEGIN
      CREATE TABLE #pkeyuniq
        (constid        INT
       , keyname        SYSNAME
       , consttype      VARCHAR (2)
       , indid          INT
       , origfillfactor TINYINT
       , groupname      SYSNAME)

      SELECT @sqlcmd =
           'SELECT c.constid'
        +       ', o.name'
        +       ', o.xtype'
        +       ', i.indid'
        +       ', i.origfillfactor'
        +       ', CASE'
        +          ' WHEN f.status & 0x10 <> 0'
        +          ' THEN ' + @q + 'DEFAULT' + @q
        +          ' ELSE f.groupname'
        +        ' END'
        + ' FROM   sasi_test..sysconstraints c'
        +   ' JOIN sasi_test..sysobjects     o'
        +     ' ON o.id         = c.constid'
        +    ' AND o.parent_obj = ' + CONVERT (VARCHAR, @id)
        +   ' JOIN sasi_test..sysindexes     i'
        +     ' ON i.id    = ' + CONVERT (VARCHAR, @id)
        +    ' AND i.name  = o.name'
        +   ' JOIN sasi_test..sysindexkeys   k'
        +     ' ON k.id    = ' + CONVERT (VARCHAR, @id)
        +    ' AND k.indid = i.indid'
        +   ' JOIN sasi_test..syscolumns     s'
        +     ' ON s.id    = ' + CONVERT (VARCHAR, @id)
        +    ' AND s.colid = k.colid'
        +   ' JOIN sasi_test..sysfilegroups  f'
        +     ' ON i.groupid = f.groupid'
        + ' WHERE c.status & 0xF IN (1, 2)'
        +   ' AND c.id     = '  + CONVERT (VARCHAR, @id)
        +   ' AND s.name   = '  + @q + @columnname + @q
        +   ' AND o.xtype IN (' + @q + 'PK'        + @q
        +                  ', ' + @q + 'UQ'        + @q + ')'
        +   ' AND 1 = (SELECT COUNT (1)'
        +            ' FROM   sysindexkeys k2'
        +            ' WHERE k2.id    = k.id'
        +              ' AND k2.indid = k.indid)'

      INSERT INTO #pkeyuniq
        EXEC (@sqlcmd)

      IF EXISTS (SELECT 1
                 FROM  #pkeyuniq)
    --THEN
        BEGIN
          SELECT @constid        = constid
               , @keyname        = keyname
               , @consttype      = consttype
               , @indid          = indid
               , @origfillfactor = origfillfactor
               , @groupname      = groupname
          FROM  #pkeyuniq

          /*
          ||============================================================
          || All primary keys are unique, but not all unique constraints
          || denote primary keys.  If a PRIMARYKEY was specifically
          || requested, it's hard to justify sending back information
          || about a UNIQUE constraint.  However, if UNIQUE was
          || requested, then it does seem reasonable to send back any
          || primary key information that qualifies...
          ||============================================================
          */          IF @p_which_attrib = 'UNIQUE'
          OR @consttype      = 'PK'
        --THEN
            SELECT @attrib = 'CONSTRAINT '
                           + @keyname
                           + CASE @consttype
                               WHEN 'PK'
                               THEN ' PRIMARY KEY ('
                               WHEN 'UQ'
                               THEN ' UNIQUE '
                                  + CASE @indid
                                      WHEN 1
                                      THEN    'CLUSTERED ('
                                      ELSE 'NONCLUSTERED ('
                                    END
                             END
                           + @columnname
                           + ')'
                           + CASE @origfillfactor
                               WHEN 0
                               THEN ''
                               ELSE ' WITH FILLFACTOR = '
                                  + CONVERT (VARCHAR, @origfillfactor)
                             END
                           + CASE
                               WHEN @groupname = 'DEFAULT'
                               THEN ''
                               ELSE ' ON [' + @groupname + ']'
                             END
        --END IF
        END
    --END IF
    END
--END IF

  /*
  ||====================================================================
  || If no errors, avoid the error trap...
  ||====================================================================
  */  GOTO PRC_EXIT

PRC_ERROR:
  /*
  ||====================================================================
  || Error trap...
  ||====================================================================
  */  SELECT @retc = 1

PRC_EXIT:
  SELECT @p_out_attrib = @attrib
       , @p_out_msg    = @msg
  
  RETURN @retc

GO

Rate

5 (1)

Share

Share

Rate

5 (1)