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:

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



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

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

Related Articles