Tables which does not have identity column defined on it

Posted on 2012-08-23
Medium Priority
Last Modified: 2012-08-23
Is there a way we can find the Tables that does not have identiy increment constraint defined on it's primary key colum ?
Question by:dumpalpr
LVL 75

Accepted Solution

Anthony Perkins earned 2000 total points
ID: 38324718
Something like this:
SELECT	SCHEMA_NAME(t.schema_id), t.name
FROM	sys.tables t
		SELECT	ic.object_id
		FROM	sys.identity_columns ic 
			INNER JOIN sys.key_constraints kc ON ic.object_id = kc.parent_object_id
		WHERE	kc.type = 'PK'
		) c ON t.object_id = c.object_id
WHERE c.object_id IS NULL

Open in new window


Author Closing Comment

ID: 38324929
Thank you.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question