//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Schema Tutorials :: Schema: How do I show the columns for a table?


Schema: How do I show the columns for a table?

Here are several ways to retrieve column names from a table. There are 5 different solutions offered here; the first three are designed for SQL Server only, and the other two will also work in Access. The first provides, by far, the most comprehensive set of information. 
 
INFORMATION_SCHEMA.COLUMNS (SQL Server) 
 
This code is SQL Server 2000 only. The August 6, 2004 update added the ability to derive the column description from sysproperties. 
 
It won't work on SQL Server 7.0, because it uses new features to also return the seed, increment, and current value of columns with the IDENTITY property. It won't work on SQL Server 2005, because there is no longer a sysproperties table. See Article #2244 for changes necessary to make the procedure work in SQL Server 2005. 
 
CREATE PROCEDURE [dbo].[listTableColumns]  
    @table SYSNAME  
AS  
BEGIN  
     
SET NOCOUNT ON  
 
DECLARE @tid INT,  
    @is VARCHAR(32),  
    @ii VARCHAR(32),  
    @ic VARCHAR(32)  
 
SELECT  
    @tid = OBJECT_ID(@table),  
    @is = CAST(IDENT_SEED(@table) AS VARCHAR(32)),  
    @ii = CAST(IDENT_INCR(@table) AS VARCHAR(32)),  
    @ic = CAST(IDENT_CURRENT(@table) AS VARCHAR(32))  
 
CREATE TABLE #pkeys  
(  
    t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,  
    cn SYSNAME, ks INT, pn SYSNAME  
)  
 
INSERT #pkeys EXEC sp_pkeys @table  
 
CREATE TABLE #sc  
(  
    cn SYSNAME, formula NVARCHAR(2048)  
)  
 
INSERT #sc SELECT  
    cl.name, sc.text  
    FROM syscolumns cl  
    LEFT JOIN syscomments sc  
    ON cl.id = sc.id AND sc.number = cl.colid  
    WHERE cl.id = @tid  
 
SELECT  
 
[COLUMN NAME] = i_s.column_name,  
 
[DATA TYPE] = UPPER(DATA_TYPE)  
    + CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN  
    '(' + CAST(NUMERIC_PRECISION AS VARCHAR)  
    + ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'  
    ELSE '' END  
    + CASE COLUMNPROPERTY(@tid, COLUMN_NAME, 'IsIdentity')  
    WHEN 1 THEN  
    ' IDENTITY (' + @is + ', ' + @ii + ')' ELSE '' END  
    + CASE RIGHT(DATA_TYPE, 4) WHEN 'CHAR' THEN  
    ' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END  
    + CASE IS_NULLABLE WHEN 'No' THEN ' NOT ' ELSE ' ' END  
    + 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,  
    2, LEN(COLUMN_DEFAULT)-2), ''),  
 
[CURRENT IDENTITY] = CASE COLUMNPROPERTY(@tid, COLUMN_NAME, 'IsIdentity')  
    WHEN 1 THEN @ic ELSE '' END,  
 
[FORMULA] = CASE COLUMNPROPERTY(@tid, COLUMN_NAME, 'IsComputed')  
    WHEN 1 THEN (SELECT SUBSTRING(formula, 2, len(formula)-2)  
    FROM #sc WHERE cn=i_s.column_name)  
    ELSE '' END,  
 
[PRIMARY KEY?] = CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE '' END, 
 
[COLUMN DESCRIPTION] = COALESCE(s.value, '') 
 
FROM  
    INFORMATION_SCHEMA.COLUMNS i_s  
LEFT OUTER JOIN  
    #pkeys pk  
ON 
    pk.cn = i_s.column_name  
LEFT OUTER JOIN 
    sysproperties s 
ON 
    s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)  
    AND s.smallid = i_s.ORDINAL_POSITION  
    AND s.name = 'MS_Description'  
WHERE  
    i_s.TABLE_NAME = @table 
ORDER BY 
    i_s.ORDINAL_POSITION 
 
DROP TABLE #pkeys  
DROP TABLE #sc  
 
END  
GO
 
Sample usage: 
 
CREATE TABLE [dbo].[fakeTable] 

    [id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY, 
    [dt] [smalldatetime] NOT NULL DEFAULT (getdate()), 
    [body] [varchar] (255) NOT NULL DEFAULT (''), 
    [email] [varchar] (255) NULL, 
    [y] AS (datepart(year,[dt])) 

GO 
 
EXEC listTableColumns 'faketable' 
 
DROP TABLE fakeTable
 
sp_help (SQL Server) 
 
This is a stored procedure you can use in SQL Server, which returns 6 recordsets when there are no constraints, and 7 recordsets when there are constraints. You might have a query like this: 
 
<% 
    dbname = "databasename" 
    tablename = "tablename" 
 
    ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _ 
        "Data Source=<x.x.x.x>;" & _ 
        "User Id=<uid>; Password=<pwd>;" & _ 
        "Initial Catalog=" & dbname 
     
    set adodbConn = CreateObject("ADODB.Connection") 
    adodbConn.Open ConnStr 
    set rs = conn.execute("EXEC sp_help '" & tablename & "'") 
    ' ... 
%>
 
And this would return the following columns, in successive recordsets: 
 
Name, Owner, Type, Created_Datetime 
 
Column_name, Type, Computed, Length, Prec, Scale, Nullable, TrimTrailingBlanks, FixedLenNullInSource 
 
Identity, Seed, Increment, Not For Replication 
 
RowGuidCol 
 
Data_located_on_filegroup 
 
Index_name, Index_description, Index_keys 
 
Constraint_type, Constraint_name, Status_enabled, Status_for_replication, Constraint_keys
 
sp_columns (SQL Server) 
 
Again, this is a system stored procedure in SQL Server. Note that <uid> needs to have at least 'datareader' access to <dbname>. 
 
<% 
    dbname = "databasename" 
    tablename = "tablename" 
 
    ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _ 
        "Data Source=<x.x.x.x>;" & _ 
        "User Id=<uid>; Password=<pwd>;" & _ 
        "Initial Catalog=" & dbname 
 
    set adodbConn = CreateObject("ADODB.Connection") 
    adodbConn.Open ConnStr 
    sql = "EXEC sp_columns @table_name='" & tablename & "'" 
    set rs = adodbConn.execute(sql) 
    do while not rs.eof 
        response.write rs("column_name") & " [" & rs("type_name") 
        if rs("type_name")="varchar" or rs("type_name")="char" then 
            response.write " (" & rs("length") & ")" 
        end if          
        response.write "]<br>" 
        rs.movenext 
    loop 
    rs.close: set rs = nothing 
    adodbConn.Close: set adodbConn = nothing 
%>
 
If you are only interested in SQL Server column names, you can execute one of the following queries: 
 
SELECT name 
    FROM syscolumns 
    WHERE [id] = OBJECT_ID('tablename') 
 
SELECT column_name 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE table_name='tablename'
 
The latter is preferred, since it is a view that is less likely to be adversely affected by changes in the underlying system tables. 
 
ADOX (Access, SQL Server) 
 
This code uses the ADOX library to extract metadata about the table. 
 
<% 
    dbname = "databasename" 
    tablename = "tablename" 
 
    ' Use this string if using Access: 
    ' ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" 
    ' ConnStr = ConnStr & "<path>\" & dbname & ".mdb" 
 
    ' Use this string is using SQL Server: 
    ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _ 
        "Data Source=<x.x.x.x>;" & _ 
        "User Id=<uid>; Password=<pwd>;" & _ 
        "Initial Catalog=" & dbname 
 
    dim columnTypes(205) 
    columnTypes(0) = "Empty" 
    columnTypes(2) = "SmallInt" 
    columnTypes(3) = "Integer" 
    columnTypes(4) = "Real" 
    columnTypes(5) = "Double" 
    columnTypes(6) = "Currency" 
    columnTypes(7) = "Date" 
    columnTypes(8) = "BSTR" 
    columnTypes(9) = "IDispatch" 
    columnTypes(10) = "Error Code" 
    columnTypes(11) = "Boolean" 
    columnTypes(12) = "Variant" 
    columnTypes(13) = "IUnknown" 
    columnTypes(14) = "Decimal" 
    columnTypes(16) = "TinyInt" 
    columnTypes(17) = "Unsigned TinyInt (BYTE)" 
    columnTypes(18) = "Unsigned Small Int (WORD)" 
    columnTypes(19) = "Unsigned Int (DWORD)" 
    columnTypes(20) = "BigInt" 
    columnTypes(21) = "Unsigned Big Int" 
    columnTypes(64) = "FileTime" 
    columnTypes(72) = "Unique Identifier (GUID)" 
    columnTypes(128) = "Binary" 
    columnTypes(129) = "Char" 
    columnTypes(130) = "nChar" 
    columnTypes(131) = "Numeric" 
    columnTypes(132) = "User Defined (UDT)" 
    columnTypes(133) = "DBDate" 
    columnTypes(134) = "DBTime" 
    columnTypes(135) = "SmallDateTime" 
    columnTypes(136) = "Chapter" 
    columnTypes(138) = "Automation (PropVariant)" 
    columnTypes(139) = "VarNumeric" 
    columnTypes(200) = "VarChar" 
    columnTypes(201) = "Text" 
    columnTypes(202) = "nVarChar" 
    columnTypes(203) = "nText" 
    columnTypes(204) = "VarBinary" 
    columnTypes(205) = "Image" 
 
    set adoxConn = CreateObject("ADOX.Catalog") 
    set adodbConn = CreateObject("ADODB.Connection") 
    adodbConn.open ConnStr 
    adoxConn.activeConnection = adodbConn 
    set table = adoxConn.Tables(tablename) 
    for each column in table.columns 
        response.write column.name & " [" & columnTypes(column.type) 
        ct = column.type 
        if ct = 129 or ct = 130 or ct = 200 or ct = 202 then 
            ' definedSize only works in SQL Server 
            Response.write " (" & column.definedSize & ")" 
        end if 
        Response.Write "]<br>" 
    next 
    set table = nothing 
    adodbConn.close: set adodbConn = nothing 
    set adoxConn = nothing 
%>
 
Recordset properties (Access, SQL Server) 
 
This query takes the column name and datatype from a simple recordset object. I modified the code to add the IsAutoIncrement property, so you can tell if a column is AutoNumber/AutoIncrement. 
 
<% 
    dbname = "databasename" 
    tablename = "tablename" 
 
    ' Use this string if using Access: 
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" 
    ConnStr = ConnStr & "<path>\" & dbname & ".mdb" 
 
    ' Use this string if using SQL Server: 
    ' ConnStr = "Provider=SQLOLEDB; Network=DBMSSOCN;" & _ 
    '     "Data Source=<x.x.x.x>;" & _ 
    '     "User Id=<uid>; Password=<pwd>;" & _ 
    '     "Initial Catalog=" & dbname 
 
    dim columnTypes(205) 
    columnTypes(0) = "Empty" 
    columnTypes(2) = "SmallInt" 
    columnTypes(3) = "Integer" 
    columnTypes(4) = "Real" 
    columnTypes(5) = "Double" 
    columnTypes(6) = "Currency" 
    columnTypes(7) = "Date" 
    columnTypes(8) = "BSTR" 
    columnTypes(9) = "IDispatch" 
    columnTypes(10) = "Error Code" 
    columnTypes(11) = "Boolean" 
    columnTypes(12) = "Variant" 
    columnTypes(13) = "IUnknown" 
    columnTypes(14) = "Decimal" 
    columnTypes(16) = "TinyInt" 
    columnTypes(17) = "Unsigned TinyInt (BYTE)" 
    columnTypes(18) = "Unsigned Small Int (WORD)" 
    columnTypes(19) = "Unsigned Int (DWORD)" 
    columnTypes(20) = "BigInt" 
    columnTypes(21) = "Unsigned Big Int" 
    columnTypes(64) = "FileTime" 
    columnTypes(72) = "Unique Identifier (GUID)" 
    columnTypes(128) = "Binary" 
    columnTypes(129) = "Char" 
    columnTypes(130) = "nChar" 
    columnTypes(131) = "Numeric" 
    columnTypes(132) = "User Defined (UDT)" 
    columnTypes(133) = "DBDate" 
    columnTypes(134) = "DBTime" 
    columnTypes(135) = "SmallDateTime" 
    columnTypes(136) = "Chapter" 
    columnTypes(138) = "Automation (PropVariant)" 
    columnTypes(139) = "VarNumeric" 
    columnTypes(200) = "VarChar" 
    columnTypes(201) = "Text" 
    columnTypes(202) = "nVarChar" 
    columnTypes(203) = "nText" 
    columnTypes(204) = "VarBinary" 
    columnTypes(205) = "Image" 
 
    set adodbConn = CreateObject("ADODB.Connection") 
    adodbConn.open ConnStr 
    set rs = adodbConn.Execute("select * from " & tablename) 
    for each column in rs.fields 
        Response.Write column.name & " [" & columnTypes(column.type) 
        ct = column.type 
        if ct = 129 or ct = 130 or ct = 200 or ct = 202 then 
            ' definedSize only works in SQL Server 
            Response.write " (" & column.definedSize & ")" 
        end if 
        Response.Write "]"  
        if column.Properties("IsAutoIncrement") then 
            response.write " (AutoNumber)" 
        end if 
        Response.Write "<br>" 
    next 
    rs.close: set rs = nothing 
    adodbConn.close: set adodbConn = nothing 
%>

Related Articles

Schema: How do I list all the indexes in a database?
Schema: How do I list the databases on my server?
Schema: How do I show all the primary keys in a database?
Schema: How do I show all the triggers in a database?
Schema: How do I show the description property of a column?
Schema: How do I show the parameters for a function or stored procedure?
Schema: How do I show the stored procedures in a database?
Schema: How do I show the tables in a database?
Schema: How do I show the user-defined functions (UDFs) in a database?
Schema: How do I show the views in a SQL Server database?

 

 


Created: 7/16/2000 | Last Updated: 8/11/2004 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (85)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...