IDENTITY Column tables List in SQL Server [Resolved]

Posted by Sureshij under Sql Server on 2/28/2014 | Points: 10 | Views : 786 | Status : [Member] | Replies : 3
How to get the list of Identity column tables in database?
I need to prepare clean up script for a database...

That is the reason i need to reset identity column values to 1 whenever the clean up completed
It is very easy to prepare RESET Identity value if i have list of identity tables.
Please give me query for getting list of identity column tables

ij


Responses

Posted by: Bandi on: 2/28/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
SELECT 
[schema] = OBJECT_SCHEMA_NAME([object_id]),
[table] = OBJECT_NAME([object_id]),
'DBCC CHECKIDENT (''' + OBJECT_SCHEMA_NAME([object_id]) + '.'+OBJECT_NAME([object_id])+ ''', RESEED, 0)'
FROM sys.identity_columns;


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sureshij on: 2/28/2014 [Member] Starter | Points: 25

Up
0
Down
Thank you for sharing query

ij

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sravan661 on: 2/28/2014 [Member] Bronze | Points: 25

Up
0
Down
Hi


SELECT
[schema] = s.name,
[table] = t.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
);




sravan

Sureshij, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response