List all Constraints in Sql Server

Hi All,
          Yesterday, I was facing a problem. Actually I was to rename all the constraints as our Standards. When I create tables in query analyzer, Sql Server create Constraints name itself. Now, I have to change all constraints according to naming conventions. This is the below script by which I get all the constraints in my database.


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName,  OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc LIKE '%CONSTRAINT'

Above script list all constraints. But If I want to see only Primary keys or Foreign Keys or default constraints the I will use the where clause like this:


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT';


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc = 'FOREIGN_KEY_CONSTRAINT';


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc = 'DEFAULT_CONSTRAINT';


SELECT OBJECT_NAME(OBJECT_ID) AS ConstraintName, SCHEMA_NAME(schema_id) AS SchemaName, OBJECT_NAME(parent_object_id) AS TableName, type_desc AS ConstraintType
FROM sys.objects WHERE type_desc = 'UNIQUE_CONSTRAINT';


I hope this will be very useful to see all database constraints.

Find all dependent objects of an Object

You can use this system Stored Procedure to find the all dependencies of an Object.

EXEC sp_depends @objname = N'StoredProcedureName' ;

How to check the SQL Server Varsion, Name & Edition

WE can see the Sql Server Name, version, edition etc using below query:

SELECT @@SERVERNAME , 

SERVERPROPERTY('productversion'), 

SERVERPROPERTY ('productlevel'), 

SERVERPROPERTY ('edition')

View all Database File Sizes

SELECT s_mf.[name], s_mf.physical_name, s_mf.[size]/128 AS SizeInMB, s_mf.max_size/128 AS MaxSizeInMB, s_mf.growth

FROM sys.master_files s_mf

ORDER BY s_mf.[size] DESC

Above query will return Database Name, its physical path & database files size in MB.

Happy Coding !!