Equivalent tsql for sql server 2000 is needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Equivalent tsql for sql server 2000 is needed

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-11-19 : 06:08:23

Can anyone please give me the equivalent sql for sql server 2000 for the following two queries

1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename',
case y.name
when 'varchar' then convert(varchar, c.max_length)
when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)
else ''
end attrib,y.*
from sys.tables t, sys.columns c, sys.types y
where t.object_id = c.object_id
and t.name not in ('sysdiagrams')
and c.system_type_id = y.system_type_id
and c.system_type_id = y.user_type_id
order by t.name, c.column_id

2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_key
from sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns c
where t.object_id = i.object_id
and t.object_id = ic.object_id
and t.object_id = c.object_id
and i.index_id = ic.index_id
and c.column_id = ic.column_id
and t.name not in ('sysdiagrams')
order by t.name, i.index_id, ic.index_column_id

This sql is extracting some sort of the information about the structure of the sql server data base[2005 version i also need this for sql server 2000 version]

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 09:05:06

why don't you use INFORMATION_SCHEMA ? It will works on both SQL 2000 and 2005

KH
[spoiler]Time is always against us[/spoiler]

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-11-19 : 11:08:13

I will accept any thing which will work in both.
Can you help me in the tsql with that ?

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 11:20:08

Why not check out the BOOKS ON LINE on INFORMATION_SCHEMA
http://msdn2.microsoft.com/en-us/library/ms186778.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources