LIST OF THINGS IN SQL SERVER 2005 — Very interesting.

FIND PRIMARY KEYS OF ALL THE TABLES IN A DATABASE.
____________________________________________________

SELECT SCHEMA_NAME(schema_id) as ‘Schema Name’,OBJECT_NAME(parent_object_id) AS ‘Table Name’,OBJECT_NAME(object_id) AS ‘Primary Key Name’, create_date , modify_date
FROM   sys.objects
WHERE  type_desc IN (‘PRIMARY_KEY_CONSTRAINT’) and type = ‘PK’

FIND LIST OF FORIEGN KEYS IN A DATABASE
____________________________________________________

SELECT SCHEMA_NAME(schema_id) as ‘Schema Name’,OBJECT_NAME(object_id) AS ‘Table Name’, create_date , modify_date
FROM   sys.objects
WHERE  type_desc IN (‘FOREIGN_KEY_CONSTRAINT’) and type = ‘F’

FIND LIST OF STORED PROCEDURES IN A DATABASE
____________________________________________________

SELECT SCHEMA_NAME(schema_id) as ‘Schema Name’,OBJECT_NAME(object_id) AS ‘Stored Procedure Name’ , create_date , modify_date
FROM   sys.objects
WHERE  type_desc IN (‘SQL_STORED_PROCEDURE’) and type = ‘P’

FIND LIST OF USER TABLES IN A DATABASE
____________________________________________________

SELECT SCHEMA_NAME(schema_id) as ‘Schema Name’,OBJECT_NAME(object_id) AS ‘Table Name’, create_date , modify_date
FROM   sys.objects
WHERE  type_desc IN (‘USER_TABLE’) and type = ‘U’

CHECK out the below table for details.

SELECT * from sys.objects

Pawan Kumar
Pawankkmr@hotmail.com

Advertisements