Tables which does not have identity column defined on it

Posted on 2012-08-23
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

    Something like this:
    SELECT	SCHEMA_NAME(t.schema_id),
    FROM	sys.tables t
    	LEFT JOIN (
    		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

    Thank you.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now