How to get list of all tables with Constraints in sql server

How to get list of all tables with Constraints in sql server?

There are different to get this information. Remember that all objects in a database is stored with an id in sys.objects table with its type.

If you do

Select *  FROM sys.objects, you can see name, object_ID, principal_ID, schema_ID, parent_object_ID, type and type_desc with other information.

Type generally give you what kind of an object it is.

So this list will give you all the object type.

 Select distinct type  FROM sys.objects;

So to find all the table with Constraints, we can write simple SQL like this.

SELECT OBJECT_NAME(parent_object_id), type, type_desc  FROM sys.objects WHERE type_desc LIKE '%cons%'

Another way to get same information is to use  Table_Constraints. This table hold information about only those tables which has constraints.

SELECT *
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

Connecting from one server to another server using SQLCMD in Management Studio

In SQL Server Management Studio, most of the time, we have to change connection to different server to run same query to validate SQL Queries. To do so, we always use change connection feature by right clicking mouse and then changing to server where we want to run our queries.

There is another way to do so without going through all this using SQLCMD Command.

To do so add SQLCMD to your tool bar.

Then

Run this.

Select @@ServerName --> This give your current server Name
GO

:Connect ServerName --> This will change to your different serverName
Go

USE DatabaseName;
GO


Select GETUTCDATE()
Run your query here

That's it.