October 9, 2009 at 2:06 pm
Hi,
Is there a way to check if any of the tables in a database have the "identity_insert" option enabled/disabled?
Thanks for the help,
S
--
:hehe:
October 9, 2009 at 2:51 pm
Assuming SS2K5 statement below should do the trick...
select is_identity, count(*) from sys.columns group by sys.columns.is_identity
0 => Disabled
1 => Enabled
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 9, 2009 at 3:15 pm
Thank, this definitely points me to the right direction.
However, this only gives me the count of the total tables in a database with the is_identity true/false.
I need names of each table and the value of is_identity.
I kind of looked in sys.objects, to join on object_id to see if I could pull the table names, but it's much more complicated than just that. I'm thinking i'd need to join to a database, and then to a table and then I believe its normalized so that I will have to join on id's to another table which ultimately contains the physical table name.
Below is how far I got without avail:
select sys.objects.type_desc, sys.columns.is_identity from sys.objects
inner join sys.columns on sys.objects.object_id = sys.columns.object_id
where sys.objects.type = 'U'
Thanks,
S
--
:hehe:
October 9, 2009 at 9:35 pm
SELECT *
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMNPROPERTY -- get columns where is_identity = 1
(
OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)) -- table ID
,column_name
,'isidentity'
) = 1
ORDER BY
table_name
,ordinal_position
Mj
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply