Presents your  
SQL SERVER E-NEWSLETTER for November 4, 2003 
 
<-------------------------------------------> 
  
DISPLAY THE STRUCTURE OF A TABLE   
  
The first database I ever programmed was dBASE II. One of my favorite 
commands was disp stru, which was short for display structure; it
described the structure of a table in a nice, convenient format.   
  
When I began using SQL Server, I was irritated that I couldn't do the 
same thing easily. Although I can use the built-in stored procedure
SP_HELP 
to describe a table, its output doesn't make it easy to put it to other 
uses. For instance, SP_HELP does three separate SELECTs and supplies more 
information than I typically want. So, I rolled my own, as you can see 
below:   
  
CREATE Procedure DispStru   
    (   
        @TableName varchar(50)   
    )   
  
As   
    IF EXISTS (SELECT * FROM dbo.sysobjects   
        WHERE id = OBJECT_ID(N'[dbo].['+@tableName+']')   
            AND OBJECTPROPERTY(id, N'IsUserTable') = 1)   
        SELECT   
            cols.name as 'Name',   
            typs.name as 'Type',   
            cols.Length,   
            cols.prec as 'Precision',   
            cols.Scale,   
            Allownulls as 'Allow Nulls'   
        FROM syscolumns cols   
            INNER JOIN systypes typs ON cols.xusertype=typs.xusertype   
        WHERE id = OBJECT_ID(@tableName)   
--        ORDER BY name   
    ELSE   
        PRINT 'No table named '+@tableName + ' in the ' + db_name() + ' Database'   
  
    RETURN   
  
Note the use of the functions OBJECT_ID() and OBJECTPROPERTY(). The 
latter expects its first parameter to be an integer that identifies the 
object of interest. This value is obtained by using the OBJECT_ID()
call; 
then, we pass it to OBJECTPROPERTY() along with the property of
interest.   
  
The ORDER BY predicate is commented out. I did that because the
disp stru command I was emulating doesn't order the columns by name.

If you prefer to order the columns alphabetically, you simply uncomment that line.   
  
NOTE: Depending on what you want to do with the output, you might want 
to rewrite this procedure as a user-defined table function. This choice 
would, for example, enable you to UNION several calls.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   
  
----------------------------------------