Database Schema definetion

Today's post is about how we can get schema definition of the current db and how we can get tables names, its columns names, Pk & Fk columns

to get the schema definition of Db

SELECT f.name AS ForeignKey,
SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName,
f.update_referential_action_desc AS UpdateAction,
f.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
JOIN sys.objects o
ON f.parent_object_id = o.object_id
ORDER BY SchemaName ASC,TableName ASC,ColumnName ASC

To get the list of Tables in Database

select TABLE_NAME from information_schema.tables where Table_Type = 'BASE TABLE' order by TABLE_NAME

To get the columns name, Data type, Length, Null able of a Table

select Column_Name, data_Type, character_maximum_length,IS_nullable
from information_schema.columns
where Table_Name = ''

Get Pk Columns of a Table

EXEC sp_Pkeys 'Table name'

To get the Fks of a table you can use the upper most query

To get the all Sps of the data base use the query

SELECT *
FROM sys.procedures;

To get only user defined Sps use the following query

Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

To get all User defined function from database, use query below

SELECT name AS [UDFName]
,create_date as [CreationDate]
,modify_date as [ModificationDate]
,type_desc as [FunctionType]
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'
ORDER BY [UDFName]

To get any Constraints on Table use the following query

EXEC sp_helpConstraint 'Table Name'