Need to know if the NOT FOR REPLICATION is on

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

Need to know if the NOT FOR REPLICATION is on

Author

Topic

magictech
Starting Member

44 Posts

Posted - 2004-11-08 : 16:56:51

The script below would show if there is a primary key, clustered index and an identity column is on a table. However, I would also like to know if NOT FOR REPLICATION is also turn.

Here is the script.

select table_name,
IDNTY = objectproperty(object_id(TABLE_NAME), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(TABLE_NAME), 'TableHasClustIndex'),
PK = objectproperty(object_id(TABLE_NAME), 'TableHasPrimaryKey')

from information_schema.tables
where table_type = 'base table'

The t-sql below would show if the NOT FOR REPLICATION is on. I'm having problem incorporating both script in the same result set. Can someone tell me what I'm doing wrong.

NFR = objectproperty((object_id(TABLE_NAME), 'CnstIsNotRepl').

Here is my final script. But for some reason I'm not getting the result that I'm expecting.

select table_name,
IDNTY = objectproperty(object_id(TABLE_NAME), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(TABLE_NAME), 'TableHasClustIndex'),
PK = objectproperty(object_id(TABLE_NAME), 'TableHasPrimaryKey') ,
NFR = objectproperty((object_id(TABLE_NAME), 'CnstIsNotRepl')

from information_schema.tables
where table_type = 'base table'

Regards

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-09 : 00:21:19

quote:
Originally posted by magictech

The script below would show if there is a primary key, clustered index and an identity column is on a table. However, I would also like to know if NOT FOR REPLICATION is also turn.

Here is the script.

select table_name,
IDNTY = objectproperty(object_id(TABLE_NAME), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(TABLE_NAME), 'TableHasClustIndex'),
PK = objectproperty(object_id(TABLE_NAME), 'TableHasPrimaryKey')

from information_schema.tables
where table_type = 'base table'

The t-sql below would show if the NOT FOR REPLICATION is on. I'm having problem incorporating both script in the same result set. Can someone tell me what I'm doing wrong.

NFR = objectproperty((object_id(TABLE_NAME), 'CnstIsNotRepl').

Here is my final script. But for some reason I'm not getting the result that I'm expecting.

select table_name,
IDNTY = objectproperty(object_id(TABLE_NAME), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(TABLE_NAME), 'TableHasClustIndex'),
PK = objectproperty(object_id(TABLE_NAME), 'TableHasPrimaryKey') ,
NFR = objectproperty((object_id(TABLE_NAME), 'CnstIsNotRepl')

from information_schema.tables
where table_type = 'base table'

Regards

excess open parentheses?

--------------------
keeping it simple...

TimS
Posting Yak Master

198 Posts

Posted - 2004-11-09 : 15:34:33

Try Below

select tab.TABLE_SCHEMA, tab.table_name, ctu.CONSTRAINT_NAME,
IDNTY = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasClustIndex'),
PK = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasPrimaryKey') ,
NFR = objectproperty(object_id(ctu.CONSTRAINT_SCHEMA + '.' + ctu.CONSTRAINT_NAME), 'CnstIsNotRepl')
from information_schema.tables tab
JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu ON tab.TABLE_NAME = ctu.TABLE_NAME AND tab.TABLE_SCHEMA = ctu.TABLE_SCHEMA
where table_type = 'base table'
UNION ALL
select tab.TABLE_SCHEMA, tab.table_name, NULL AS CONSTRAINT_NAME,
IDNTY = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasClustIndex'),
PK = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasPrimaryKey') ,
NFR = NULL
from information_schema.tables tab
where table_type = 'base table' AND NOT EXISTS(
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu
WHERE tab.TABLE_NAME = ctu.TABLE_NAME AND tab.TABLE_SCHEMA = ctu.TABLE_SCHEMA
)

magictech
Starting Member

44 Posts

Posted - 2004-11-11 : 10:41:11

Thanks for your respond Tims. The result set has four columns. The NFR columns suppose to show if NOT FOR REPLICATION is turn on or off (which is the default) on each table. But, instead the result set show zeros for every table that has a primary key and NULL for every table that doesn't have a primary key. I was hoping for a script that would indicate if NOT FOR REPLICATION is on by showing the value 1 or 0 if NOT FOR REPLICATION is not on.

TimS
Posting Yak Master

198 Posts

Posted - 2004-11-11 : 14:35:26

NOTE: I don't Think a table has a property NOT FOR REPLICATION!
I think only parts of the table has this property.

This query below does it for Identity Columns.

Tim S

select tab.TABLE_SCHEMA, tab.table_name,
IDNTY = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasClustIndex'),
PK = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasPrimaryKey') ,
NFR = COLUMNPROPERTY ( object_id(tab.TABLE_SCHEMA + '.' + tab.table_name) , col.COLUMN_NAME , 'IsIdNotForRepl' )
from information_schema.tables tab
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col ON tab.TABLE_NAME = col.TABLE_NAME AND tab.TABLE_SCHEMA = col.TABLE_SCHEMA AND
COLUMNPROPERTY ( object_id(tab.TABLE_SCHEMA + '.' + tab.table_name) , col.COLUMN_NAME , 'IsIdentity' ) = 1
where table_type = 'base table'

magictech
Starting Member

44 Posts

Posted - 2004-11-11 : 15:07:05

Thanks a lot Tims. I really appreciate your help. The new script work exactly the way I wanted. Thanks again.

Later

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources