Information_schema.columns was introduced in SQL 7, but for some reason it disappeared in SQL 2000; then come back in SQL 2005.
When I upgraded to SQL 2000, I simply copy the code from SQL 7, and create my dbo.columns. Here is the script for SQL2000 only.
Enjoy it.
go
--Displays columns accessable to the current user
create view [dbo].[COLUMNS]
as
select
db_name() as TABLE_CATALOG
,user_name(obj.uid) as TABLE_SCHEMA
,obj.name as TABLE_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,com.text as COLUMN_DEFAULT
,case col.isnullable
when 1 then 'YES'
else 'No '
end as IS_NULLABLE
,spt_dtp.LOCAL_TYPE_NAME as DATA_TYPE
,convert(int,
OdbcPrec(col.xtype, col.length, col.xprec)
+ spt_dtp.charbin) as CHARACTER_MAXIMUM_LENGTH
,convert(int, spt_dtp.charbin +
case when spt_dtp.LOCAL_TYPE_NAME in ('nchar', 'nvarchar', 'ntext')
then 2*OdbcPrec(col.xtype, col.length, col.xprec)
else OdbcPrec(col.xtype, col.length, col.xprec)
end) as CHARACTER_OCTET_LENGTH
,nullif(col.xprec, 0) as NUMERIC_PRECISION
,spt_dtp.RADIX as NUMERIC_PRECISION_RADIX
,col.scale as NUMERIC_SCALE
,spt_dtp.SQL_DATETIME_SUB as DATETIME_PRECISION
,convert(sysname, NULL) as CHARACTER_SET_CATALOG
,convert(sysname, NULL) as CHARACTER_SET_SCHEMA
,convert(sysname, case
when spt_dtp.LOCAL_TYPE_NAME in
('char', 'varchar', 'text')
then a_cha.name
when spt_dtp.LOCAL_TYPE_NAME in
('nchar', 'nvarchar', 'ntext')
then N'Unicode'
else NULL
end) as CHARACTER_SET_NAME
,convert(sysname, NULL) as COLLATION_CATALOG
,convert(sysname, NULL) as COLLATION_SCHEMA
,col.collation as COLLATION_NAME
,convert(sysname, case when typ.xusertype > 256
then DB_NAME()
else NULL
end) as DOMAIN_CATALOG
,convert(sysname, case when typ.xusertype > 256
then USER_NAME(obj.uid)
else NULL
end) as DOMAIN_SCHEMA
,convert(sysname, case when typ.xusertype > 256
then typ.name
else NULL
end) as DOMAIN_NAME
FROM
sysobjects obj,
master.dbo.spt_datatype_info spt_dtp,
systypes typ,
syscolumns col
LEFT OUTER JOIN syscomments com on col.cdefault = com.id
AND com.colid = 1,
master.dbo.syscharsets a_cha --charset/1001, not sortorder.
WHERE
permissions(obj.id, col.name) != 0
AND obj.id = col.id
AND typ.xtype = spt_dtp.ss_dtype
AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2)
AND obj.xtype in ('U', 'V')
AND col.xusertype = typ.xusertype
AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)
AND a_cha.id = isnull(convert(tinyint, CollationPropertyFromID(col.collationid, 'sqlcharset')),
convert(tinyint, ServerProperty('sqlcharset'))) -- make sure there's one and only one row selected for each column
go