Convert query with system objects from SQL 2000 to 2005/2008
- by Dan
I have some SQL I need to get working on SQL 2005/2008. The SQL is from SQL 2000 and uses some system objects to make it work.
master.dbo.spt_provider_types
master.dbo.syscharsets
systypes
syscolumns
sysobjects
I know SQL 2005 does no longer use system tables and I can get the same information from views, but I am looking for a solution that will work for both SQL 2000 and 2005/2008. Any ideas?
select top 100 percent
TABLE_CATALOG = db_name(),
TABLE_SCHEMA = user_name(o.uid),
TABLE_NAME = o.name,
COLUMN_NAME = c.name,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_COMPUTED = convert(bit, c.iscomputed)
from
syscolumns c left join syscomments m on c.cdefault = m.id and m.colid = 1,
sysobjects o,
master.dbo.spt_provider_types d,
systypes t,
master.dbo.syscharsets a_cha /* charset/1001, not sortorder.*/
where
o.name = @table_name and
permissions(o.id, c.name) <> 0
and (o.type in ('U','V','S') OR (o.type in ('TF', 'IF') and c.number = 0))
and o.id = c.id
and t.xtype = d.ss_dtype
and c.length = case when d.fixlen > 0 then d.fixlen else c.length end
and c.xusertype = t.xusertype
and a_cha.type = 1001 /* type is charset */
and a_cha.id = isnull(convert(tinyint, CollationPropertyFromID(c.collationid, 'sqlcharset')),
convert(tinyint, ServerProperty('sqlcharset'))) -- make sure there's one and only one row selected for each column
order by 2, 3, c.colorder
) tbl where IS_COMPUTED = 0
