System tables
-
Hi,
I'm new to MS SQL, and I'm trying to port code from Interbase/Firebird to MS SQL. It's actually a replication tool that I'm extending to be able to function with MS SQL, so I need to be able to support as many different versions as possible...
So my question is, could someone explain (or perhaps refer to a document somewhere) what are the main differences in the system tables and in trigger/SP code between version 8 (MSDE, which I had been using for my tests up till now) and version 9 (SQL Server 2005 Express Edition)? I have a good deal of SQL and T-SQL which mostly works in version 8, but there seem to be some incompatibilities with version 2005.
In particular, I was previously checking sysobjects.status > 0 to see make sure the object was not a system object. This no longer works in 2005. What is the recommended way to do this in version 2005?
For a specific example, here's my query for retrieving the list of primary key fields for a given table:
select sc.name as pk_name
from sysobjects o
join sysobjects p on p.parent_obj = o.id
join sysindexes si on si.name = p.name
join sysindexkeys sik on sik.id = o.id and sik.indid = si.indid
join syscolumns sc on sc.id = o.id and sc.colid = sik.colid
where o.type='U'
and p.xtype='PK'
and o.status >0
and o.name=:table_name
order by sik.keyno
Does that make sense?
Thanks in advance for your help.
Jonathan Neve.
Microtec Communications.
http://www.microtec.fr/copycat/cc
Best Regards,Jonathan Neve
microtec.fr -
Check out BOL for different views under INFORMATION_SCHEMA.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Jonathan,
It is always better to query INFORMATION_SCHEMA views rather than directly quering the sytem table.
Microsoft recommends using INFORMATION_SCHEMA views.
Regards,
Sudheer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply