List constraints for all tables in a database

I’ve been working on a project where I’m been responsible for creating a new relational database model. As the database model grew it became quite hard to get a clear picture over naming of constraints among other things, so I decided to create a script that listed all constraints using SQL Server Management Studio.

Below you can see my solution in how to accomplish this. Just remember to rename the MyCustomSchema to your schema name, or omit it if all your tables belong to the dbo schema.

 

DECLARE @table_name varchar(50)
DECLARE @current_table_name varchar(50)

DECLARE all_tables CURSOR  
FOR SELECT Name from sys.tables

OPEN all_tables

FETCH NEXT FROM all_tables INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @current_table_name = 'MyCustomSchema.' + @table_name
EXEC sp_helpconstraint @current_table_name
FETCH NEXT FROM all_tables INTO @table_name
END

CLOSE all_tables
DEALLOCATE all_tables