Sysobjects question

[TitleistDBA]

I'm look to see if any of my Foreign Key constraints have been disabled. Is there an easy way to do this?
I have run the following select which will show my all my FK constraints.
select * from sysobjects
where xtype = 'F'
How can I tell if any have been disabled with the following syntax.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
Thanks

http://www.acushnetcompany.com

http://www.titleist.com

 

[mrdenny]

By using the OBJECTPROPERTY function.

select OBJECTPROPERTY(object_id('FK_Orders_Customers'), 'CnstIsDisabled')

Where FK_Orders_Customers is the name of the key.

If you run this in Northwind it will return 0 (Enabled).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TitleistDBA]

Mr Denny.
Thank you very much. the only problem I have is there are hundreds of FK's and I don't know which one could be disabled. The company which provided us this product also gave the developers a script to disable all constraints. Why they would do that I have no idea. Anyway, I am trying to write a script to go through all the constaints and check the CnstIsDisabled property.



DECLARE @object sysname,
@cmd varchar(500)

CREATE TABLE ##TMP(name sysname null,
chk int null)

INSERT INTO ##TMP(name, chk)
SELECT name, 0
FROM sysobjects
WHERE xtype = 'F'

While @@rowcount > 0
Set rowcount 1
select @object = name
from ##TMP
where chk = 0

SELECT @cmd = 'select OBJECTPROPERTY(object_id('+ @object+'), "CnstIsDisabled")'

exec @cmd

update ##TMP
SET chk = 1
WHERE name = @object

When I run this script I get the following error.

(13 row(s) affected)

Server: Msg 203, Level 16, State 2, Line 21
The name 'select OBJECTPROPERTY(object_id(FK_Orders_Customers), "CnstIsDisabled")' is not a valid identifier.

I think it has something to do with the quotes. Any suggestions.

http://www.acushnetcompany.com

http://www.titleist.com

 

[TitleistDBA]

I found a better way. I'll just do this to build my script.

SELECT 'select OBJECTPROPERTY(object_id('+
name
+'),CnstIsDisabled)'
FROM sysobjects
WHERE xtype = 'F'

http://www.acushnetcompany.com

http://www.titleist.com