How to find IDENTITY column of a table?

At times when we are working on a large database we know most of the tables and their columns , but in case you don't know or you don't have designed database then you may need to find identity column of a particular table like i needed to find today.
Let see how we can do it
create a test table

-- Create a table
CREATE TABLE [dbo].[BOOKS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [varchar](40) NULL,
)
GO
-- Inserting values into BOOKS table.
INSERT INTO BOOKS (NAME) VALUES ('C++')
INSERT INTO BOOKS (NAME) VALUES ('.NET')
INSERT INTO BOOKS (NAME) VALUES ('PROGRAMMING LANGUAGES')
INSERT INTO BOOKS (NAME) VALUES ('SQL')




now let see its IDENTITY column

SELECT O.NAME, C.NAME
FROM SYS.OBJECTS O
INNER JOIN SYS.COLUMNS C ON O.OBJECT_ID = C.OBJECT_ID
WHERE C.IS_IDENTITY = 1
AND O.NAME LIKE 'BOOKS'
 


which is our required.
there is another way to find it using

sp_MShelpcolumns <TableName>
like well use it this way 

sp_MShelpcolumns BOOKS
it will return multiple columns of the table but the colum having col_identity = 1 is your required one.
 here is its result 

 


i found out required column this way you are welcome to try it ;)