IDENTITY Column tables List in SQL Server [Resolved]

Posted by Sureshij under Sql Server on 2/28/2014 | Points: 10 | Views : 1257 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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


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

Latest Posts