SQL 6.5 Scripting - SQL Server Forums

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

General SQL Server Forums

Script Library

SQL 6.5 Scripting

Author

Topic

cranshark
Starting Member

6 Posts

Posted - 2005-10-07 : 13:26:08

Anyone know how to write a script for sql 6.5 that identifies what column is a primary key on a table? (INFORMATION_SCHEMA doesn't exist) Thanks for the help in advance.

Kristen
Test

22859 Posts

Posted - 2005-10-07 : 13:39:51

Blimey! 6.5 eh?!

Dunno if this helps http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41896

Kristen

cranshark
Starting Member

6 Posts

Posted - 2005-10-07 : 13:54:48

Kristen,

Yep, just started a contract to help these poor souls get up to sql 2005 from 6.5. The link you sent was helpful but 6.5 doesn't have the INFORMATION_SCHEMA view(s) that we've had the luxury of using for the past 10 years. I've been going through the system tables and I can't figure out how 6.5 knows what column is a primary key!

Kristen
Test

22859 Posts

Posted - 2005-10-07 : 14:17:48

Sorry, I glanced at that thread and thought it didn't have INFORMATION_SCHEMA

This might get you a bit closed


Select
	Case o1.xtype
		When 'C' Then 'Check'
		When 'D' Then 'Default'
		When 'F' Then 'Foreign Key'
		When 'PK' Then 'Primary Key'
		When 'UQ' Then 'Unique'
		Else 'Other' End as 'Constraint Type',
	o.name as 'Table Name',
	o1.name as 'Constraint/Index Name',
	c1.name as 'Column Name',
	k.keyno as 'KeyNo'
FROM	sysobjects o
	Join sysobjects o1
		 on o1.Parent_obj = o.id
	Join sysconstraints c
		 on c.constid = o1.id
	Join sysindexes i
		 on i.id = o.id
		and i.name = o1.name
	Join sysindexkeys k
		 on k.id = i.id
		and k.indid = i.indid
	Join syscolumns c1
		 on c1.id = k.id
		and c1.colid = k.colid
Where	o1.xtype = 'PK'
Order By o.name, o1.name, k.KeyNo

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-07 : 14:30:26

Does 6.5 have the OBJECTPROPERTY function? I can't remember if it does or if I even used it back then. If it does, then this is a lot easier.

Does your client understand that you can't upgrade from 6.5 to 2005 directly? You'll need to upgrade them to either 7.0 or 2000 first, then to 2005.

Tara

cranshark
Starting Member

6 Posts

Posted - 2005-10-07 : 14:55:19

Yes, 6.5 has an OBJECTPROPERTY metadata function.

Yes, the client knows that we will be stepping them through several versions to get there. That's why I'm trying to get some scripts done that will give me more information on their database.

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-07 : 17:47:46

Run the stored proc master.dbo.sp_help to get the primary key info:

use MyDB
exec sp_help 'MyTableName'

If you want to do it with a script, look at the code in sp_help to see how it is done

CODO ERGO SUM

cranshark
Starting Member

6 Posts

Posted - 2005-10-07 : 18:16:20

Thanks for the help. I finally extracted what's in sp_help to figure it out or at least get close enough.

JLatos
Starting Member

1 Post

Posted - 2005-10-11 : 19:11:42

Have you looked at the entity relationship diagram?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-12 : 01:00:50

quote:
Originally posted by JLatos

Have you looked at the entity relationship diagram?

That assumes that one exists. I guess you could reverse engineer it if you have the software to do it. But I think he found his solution anyway via sp_help.

Tara

cranshark
Starting Member

6 Posts

Posted - 2005-10-12 : 10:44:03

sp_help got me most of the way, so did sp_index and sp_keys. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources