INFORMATION_SCHEMA problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

INFORMATION_SCHEMA problem

Author

Topic

Tim
Starting Member

392 Posts

Posted - 2002-09-12 : 20:25:26

bizarro world !

If I run this statement against my database it will fail since the constraint already exists. That's fine and as expected.

ALTER TABLE [dbo].[TableX] ADD
CONSTRAINT [FK_TableX_Length_UOM] FOREIGN KEY
(
[Length_UOM_guid]
) REFERENCES [dbo].[UOM] (
[guid]
) NOT FOR REPLICATION

However, if I then run this statement:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

zero records are returned. Why do my constraints not show up when they are clearly in the database??

SELECT * FROM INFORMATION_SCHEMA.TABLES works fine so it's not a permission problem.

Any help would be appreciated. The database in question is on server version:

Microsoft SQL Server 7.00 - 7.00.842

I am running the query from query analyser installed with version :

Microsoft SQL Server 2000 - 8.00.194

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

sanjnep
Posting Yak Master

191 Posts

Posted - 2002-09-13 : 00:25:28

Thankx a lot for your question.
I will send U the solution whenever I find it

Thank U

'Heaven's light is our guide'

Sanjeevshrestha

Tim
Starting Member

392 Posts

Posted - 2002-09-13 : 00:26:49

well thanks, I think.

i hope folks don't skip this one thinking it's answered !

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

Tim
Starting Member

392 Posts

Posted - 2002-09-15 : 20:45:51

For those interested, I believe this is a bug.

Take a look at the code for the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view in SQL7:


--Identifies table constraints owned by current users
create view INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 as
 select
	db_name()				as CONSTRAINT_CATALOG
	,user_name(c_obj.uid)	as CONSTRAINT_SCHEMA
	,c_obj.name				as CONSTRAINT_NAME
	,db_name()				as TABLE_CATALOG
	,user_name(t_obj.uid)	as TABLE_SCHEMA
	,t_obj.name				as TABLE_NAME
	,case c_obj.xtype
					when 'C' then	'CHECK'
					when 'UQ' then	'UNIQUE'
					when 'PK' then	'PRIMARY KEY'
					when 'F' then	'FOREIGN KEY'
		 		  end		as CONSTRAINT_TYPE
	,'NO'					as IS_DEFERRABLE
	,'NO'					as INITIALLY_DEFERRED
from
	sysobjects	c_obj
	,sysobjects	t_obj
where
	c_obj.uid	= user_id()
	and t_obj.id	= c_obj.parent_obj
	and c_obj.xtype	in ('C' ,'UQ' ,'PK' ,'F')

If I query the sysobjects table, the userid is set to 1 (which is dbo user) on all the constraints.

When I log in as a user (even in the db_owner role) the user_id() function does not return 1. Therefore the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view retuns zero records.

I looked at the same view's code in SQL2000 and the call to user_id() has been replaced with a call to permissions() which makes more sense, and not surprisingly, works properly.

Still, I am stuck on SQL 7 so it looks like I am screwed for using the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view.

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-15 : 20:53:20

I do not believe a foreign key qualifies as a table constraint ... a table constraint would be a check constraint that spans multiple columns on a table...

Tim
Starting Member

392 Posts

Posted - 2002-09-15 : 20:56:39

bah...

according to Microsoft's code:

case c_obj.xtype
when 'C' then 'CHECK'
when 'UQ' then 'UNIQUE'
when 'PK' then 'PRIMARY KEY'
when 'F' then 'FOREIGN KEY'

They obviously consider foreign keys as table constraints, as do I.

The only problem is the view won't show them to me!

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

Tim
Starting Member

392 Posts

Posted - 2002-10-25 : 00:15:50

For anyone interested...

If you alias your login to dbo, rather than adding your login to the dbo role, the problem goes away.

----
Nancy Davolio: Best looking chick at Northwind 1992-2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources