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)')
Retrieve column attributes
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