Tags

, ,


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.  

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 
%>
Advertisements