Get Details information about Database, Tables, Columns and Stored Procedures in Sql Server

Sometimes it is necessary to get the Creation Date, Modification Date and detailed information about Tables, Stored Procedures, Primary Keys, Foreign Keys and default constraints of a table or in the whole database in SQL Server.

The Details information contains columns as:- name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published

Get Details information about all Tables in a Database

******************************************** 

SELECT * FROM sys.objects  WHERE  type='U'

Get Details information about all Tables with latest creation date in a Database

*******************************************************************

SELECT * FROM sys.objects  WHERE  type='U' order by create_date desc

Get Details information about all Stored Procedures in a Database

******************************************************* 

SELECT * FROM sys.objects  WHERE  type='P'

Get Details information about all Stored Procedures with latest creation date in a Database

**************************************************************************

SELECT * FROM sys.objects  WHERE  type='P' order by create_date desc

Get Details information about all Primary Key Constraint information from a Database

***********************************************************************  

SELECT * FROM sys.objects  WHERE  type='PK'

Get Details information about all Primary Key Constraint information with latest creation date from a Database

*****************************************************************************

SELECT * FROM sys.objects  WHERE  type='PK' order by create_date desc

Get Details information about all Foreign Key Constraint information from a Database

***********************************************************************  

SELECT * FROM sys.objects  WHERE  type='F'

Get Details information about all Foreign Keys with latest creation date from a Database

*************************************************************************

SELECT * FROM sys.objects  WHERE  type='F' order by create_date desc

Get Details information about all Default Constraint information from a Database

********************************************************************

SELECT * FROM sys.objects  WHERE  type='D'

Get Details information about all Foreign Keys with latest creation date from a Database

*************************************************************************

SELECT * FROM sys.objects  WHERE  type='D' order by create_date desc

Get Table Schema and Table Name from Database

******************************************

SELECT * FROM INFORMATION_SCHEMA.TABLES

Get Schema and Column information about all Columns and all Tables with Table name within a Database

******************************************************************************  

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Get Schema and Column information about all Columns for a specific table within a Database

*****************************************************************************

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

where Table_name='xyz_UserMaster'