Skip to main content

Posts

Disable/Enable All the Foreign Key Constraint in SQL Server Database

In some scenario we need to disable all foreign key relationships from all table, suppose for bad testing purpose or you want to see what will be effect of garbage data on application or performance, due to this purpose you want to insert garbage data into table and this table contains foreign key constraints then you will get the error of foreign key constraint.
Many people face this types of issue, the following query will resolve this problem but be careful on the production server, it would be better if you do this under guidance to any DBA.
The following query will disable all constraints in the database.

execsp_msforeachtable'alter table ? nocheck constraint all'

Once you disabled all constraints you should also need to enable all constraints once you completed your work, so following query will help you to enable all constraints in the database.

execsp_msforeachtable'alter table ? with check check constraint all'

Using the above query you can resolve above described s…
Recent posts

Query to find foreign key relationship and name of the constraints for each table in the database

Sometimes we need to find all foreign key on a table so that we can analyze the relation of a table and can find the solution accordingly. It is very difficult to see it manually, by using the following query we can find all foreign key relationships on the particular table.

SELECT K_Table=FK.TABLE_NAME, FK_Column=CU.COLUMN_NAME, PK_Table=PK.TABLE_NAME, PK_Column=PT.COLUMN_NAME, Constraint_Name=C.CONSTRAINT_NAME FROMINFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSC INNERJOININFORMATION_SCHEMA.TABLE_CONSTRAINTSFKONC.CONSTRAINT_NAME=FK.CONSTRAINT_NAME INNERJOININFORMATION_SCHEMA.TABLE_CONSTRAINTSPKONC.UNIQUE_CONSTRAINT_NAME=PK.CONSTRAINT_NAME INNERJOININFORMATION_SCHEMA.KEY_COLUMN_USAGECUONC.CONSTRAINT_NAME=CU.CONSTRAINT_NAME INNERJOIN( SELECTi1.TABLE_NAME,i2.COLUMN_NAME FROMINFORMATION_SCHEMA.TABLE_CONSTRAINTSi1 INNERJOININFORMATION_SCHEMA

Check if an index exists in SQL Server

Many times we come across the scenario where we need to some code based on whether an index exists or not.
The following query may help you to check an index is exists in a particular table or not. Suppose we have a table like dbo.Table1 and it has index idx_Index and in some scenario, we need to check idx_Index exists or not.

System.index catalog view records for each Clustered and Non-Clustered indexes.  We can execute the following query to find out a particular index exists or not in a particular table

IFEXISTS (   SELECT 1 FROMsys.indexes   WHEREname='idx_Index'ANDobject_id=OBJECT_ID('dbo.Table1') ) BEGIN PRINT'Index is Exists' END

Check if a primary key constraint exists in SQL server

A primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column for a table has a unique identity. Primary key constraints. A primary key (PK) is a single column or combination of columns (called a compound key) that uniquely identifies each row in a table.
Following example to check if a primary key exists

CREATETABLEPRODUCT ( IDINTNOTNULLIDENTITY(1,1), SKUVARCHAR(20)NOTNULL, TITLEVARCHAR(200)NOTNULL, PRICEMONEYNOTNULL, DESCRIPTIONVARCHAR

How to escape a single quote in SQL Server

Single quote issue is always irritating me and always I ignored this issue many times but in some scenario we can't. I searched this issue on google and found some solution and I want to share with you.
Suppose we have a string "I like codefari's article", we will escape the single quote here is the example.
SELECT'I like codefari''s article'
Please add a single quote at apostrophe s



Second example as given below
SELECT'I like codefari'+CHAR(39)+'s article'
Here you can use CHAR(39) as given above example.

Find the user who dropped database table

Once a table was missing from our database and my project manager ask to find to user who dropped the table I search on google and find the solution so I want to share my experience with you.

CREATEDATABASEmyTest GO
USEmyTest GO
CREATETABLETable1(IDINT) GO
ALTERTABLETable1 ADDFirstColVARCHAR(100) GO
DROPTABLETable1

How to find recent executed query in SQL Server

Sometimes we do R&D on the query and try to found the solution of any problem and due to some reason you shut down your system without saving the query and next you need to recover your R&D query, so using the following query you can recover your executed query.

sys.dm_exec_query_stats and sys.dm_exec_sql_text catalog the view records for all currently executed query using the following query we can find the recently executed query.


SELECTdest.TEXTAS[MyQuery],a.last_execution_timeAS[DateTime] FROMsys.dm_exec_query_statsASa CROSSAPPLYsys.dm_exec_sql_text(a.sql_handle)ASdest WHEREa.last_execution_time>dateadd(day,-1,

SSIS - How to Load Data from Excel File to SQL Server Table in SSIS Package

Welcome to codefari video tutorial, in this tutorial we will learn how to import data from excel file to SQL Server table in SSIS package. In this tutorial we will learn how to extract data from excel file, transform according to us and load in SQL Server table.







SQL Server- How to get last access/update time for tables in Database?

One day I got requirement to find last updates of tables in database. Means I want to get all the tables’ last update in database and did surfing on google and found the best way. Link:https://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/
I am taking reference from above link and try to explain. See following query it may help you to find exact result.

SELECTOBJECT_NAME(OBJECT_ID)ASDatabaseName,last_user_update,* FROMsys.dm_db_index_usage_stats WHEREdatabase_id=DB_ID('<your_db_name>')

If you want to see the last update of particular table then use the following query.

SELECTOBJECT_NAME(OBJECT_ID)ASDatabaseName,last_user_update,* FROMsys.dm_db_index_usage_stats WHEREdatabase_id=DB_ID('<your_db_name>') ANDOBJECT_ID=OBJECT_ID('table_name')