Convert query with system objects from SQL 2000 to 2005/2008

Question

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 

Answer 1

you need to read this Microsoft doc: Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views

EDIT based on OP's comments

for YourTable, my query here will list the:

• schema.tablename
• ColumnName
• TableType
• DataType
• Nullable status
• Identity, computed column, and check constraint info

this runs on SQL Server 2005, not sure about 2000.

SELECT
    sh.name+'.'+o.name AS TableName
        ,s.name as ColumnName
        ,o.type_desc AS TableType
        ,CASE
             WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
             WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE t.name
         END AS DataType

        ,CASE
             WHEN s.is_nullable=1 THEN 'NULL'
            ELSE 'NOT NULL'
        END AS Nullable
        ,CASE
             WHEN ic.column_id IS NULL THEN ''
             ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
         END
        +CASE
             WHEN sc.column_id IS NULL THEN ''
             ELSE ' computed('+ISNULL(sc.definition,'')+')'
         END
        +CASE
             WHEN cc.object_id IS NULL THEN ''
             ELSE ' check('+ISNULL(cc.definition,'')+')'
         END
            AS MiscInfo
    FROM sys.objects                           o
        INNER JOIN sys.columns                 s ON o.object_id=s.object_id
        INNER JOIN sys.types                   t ON s.system_type_id=t.system_type_id and t.is_user_defined=0
        INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
        LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
        LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
        LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
    WHERE sh.name='dbo'         --schema name
        AND o.name='YourTable'  --table name
    --AND s.name='YourColumn'
    ORDER BY s.column_id

Answer 2

If you trying to make the query work in SQL 2000 and SQL 2005, then it should mostly work as is. Microsoft created compatibility views for the system tables specifically so that legacy code would not break. The one problem might be the spt_provider_types table. For that you will either need to use something else like the INFORMATION_SCHEMA views or you can recreate the spt_ tables by running a script in the installs folder on the SQL 2005 system called u_tables.sql.