Skip to main content

How to get all tables which have primary key in SQL Server?

Some time we need to get all tables which have primary key constraints. There are many way to get this information.


Select
       ISTC.Table_Catalog as BD_Name,
       ISTC.Table_Schema AS TBL_SCHEMA,
       ISTC.Table_Name AS TBL_NAME,
       ISCCU.Column_Name AS COL_NAME,
       ISTC.Constraint_Name AS CONSTRAINT_NAME
From
       information_Schema.Table_Constraints ISTC 
INNER JOIN
       Information_Schema.constraint_column_usage ISCCU 
       on ISTC.Constraint_Name=ISCCU.Constraint_Name 
       AND ISTC.Table_Name=ISCCU.Table_Name 
where
       Constraint_Type='PRIMARY KEY'




This one is the best query to find out the tables which have primary key because information schema provides more information and all the data can be retrieved with the filtered filter.

Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special character from string in MongoDB

Problem: Suppose wehave a collection and one field is type string contains some special character (like !@#$%) and we don’t want these special character.
Solution: We can easily remove the special character from field using script “replace(/[^a-zA-Z 0-9 ]/g, '')” in our query.  How can we remove special character from string using this script please see following example.
Example: Suppose we have a collection “EduSurvey “where we are collecting information from institutions.

{Name:"JB institute”, About:"This is good one collage for MBA", Information:"This $%%institute ##has good faculty etc$$"}
{Name:"MK institute”, About:"This is good one collage for MCA", Information:"This$$%# is the dummy text12"}
{Name:"MG institute”, About:"This is good one collage for B,Tech", Information:"This# institute@ has&* good infrastructure"}

Did you notice Information fields contains some special character so we…