Category Archives: Scripts

How to check if a VIEW exists in Sql Server

Many a times we come across a scenario where we need to execute some code based on whether a View exists or not. There are different ways of identifying the View existence in Sql Server, this article will list out the commonly used approaches. Let me know which approach you use and reason for the same.

[ALSO READ] Views in Sql Server

To demonstrate these different approaches let us create a sample database SqlHintsDemoDB. Create Customers Table with sample data and a vwGetCustomerInfo View by the following script:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
--Create Customer table
CREATE TABLE dbo.Customers
( CustomerID int Identity(1,1), FirstName NVarchar(50),
  LastName NVarChar(50), Phone varchar(50))
GO
--Insert sample records into the customer table
INSERT INTO Customers (FirstName, LastName, Phone)
Values ('Kalpana','Biradar','2727272727'),
       ('Basavaraj','Biradar','1616161616')
GO
--Create view
CREATE VIEW dbo.vwGetCustomerInfo
AS
 SELECT CustomerID, FirstName +' ' + LastName FullName
 FROM CUSTOMERS
GO
SELECT * FROM dbo.vwGetCustomerInfo
GO

[ALSO READ] How to check if a Table exists in Sql Server

Approach 1: Using sys.views catalog view

We can write a query like below to check if a view vwGetCustomerInfo exists in the current database in any schema.

IF EXISTS(SELECT 1 FROM sys.views 
     WHERE Name = 'vwGetCustomerInfo')
	BEGIN
		PRINT 'View Exists'
	END

RESULT:
Check-View-Existence-using-sys.views 1

The above query checks the existence of the vwGetCustomerInfo View across all the schemas in the current database. Instead of this if you want to check the existence of the View in a specified Schema then we can re-write the above query as below:

IF EXISTS(SELECT 1 FROM sys.views 
   WHERE object_id = OBJECT_ID('dbo.vwGetCustomerInfo'))
	BEGIN
		PRINT 'View Exists'
	END

RESULT:
Check-View-Existence-using-sys.views 2

If you want to check the existence of a View in a database other than the current contextual database, then we can re-write the above query as shown below:

IF EXISTS(SELECT 1 FROM SqlHintsDemoDB.sys.views 
   WHERE object_id = OBJECT_ID('dbo.vwGetCustomerInfo'))
	BEGIN
		PRINT 'View Exists'
	END
GO

RESULT:
Check-View-Existence-using-sys.views 3
[ALSO READ] How to check if a record exists in table

Approach 2: Using sys.objects catalog view

sys.views catalog view inherits the rows from the sys.objects catalog view, sys.objects catalog view is referred to as the base view whereas sys.views is referred to as derived view. sys.views will return the rows only for the views, whereas sys.objects view apart from returning the rows for Views, it returns rows for the objects like: tables, stored procedure etc.

We can write a script like below to check the existence of a view in the current contextual database:

IF EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID('dbo.vwGetCustomerInfo')
                    AND type = 'V') 
	BEGIN
		PRINT 'View Exists'
	END

RESULT:
Check-View-Existence-using-sys.objects

Approach 3: Using sys.sql_modules Catalog View

We can use the sys.sql_modules catalog view to check the existence of the View as shown below:

USE SqlHintsDemoDB
GO
IF EXISTS (SELECT 1 FROM sys.sql_modules
   WHERE object_id =  OBJECT_ID('dbo.vwGetCustomerInfo')
   AND OBJECTPROPERTY(object_id, 'IsView') = 1) 
	BEGIN
		PRINT 'View Exists'
	END

RESULT
Check-View-Existence-using-sys.sql_modules

Approach 4: Using OBJECT_ID() function

We can use OBJECT_ID() function like below to check if the View vwGetCustomerInfo exists in the current database.

IF OBJECT_ID(N'dbo.vwGetCustomerInfo', N'V') IS NOT NULL
	BEGIN
		PRINT 'View Exists'
	END

RESULT:
Check-View-Existence-using-Object_Id function

Specifying the Database Name and Schema Name parts for the View Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the View in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that, even though the current database is MASTER database, we can check the existence of the view vwGetCustomerInfo in the dbo schema in the SqlHintsDemoDB database.

USE master	
GO
IF OBJECT_ID(N'SqlHintsDemoDB.dbo.vwGetCustomerInfo', 
                  N'V') IS NOT NULL
	BEGIN
		PRINT 'View Exists'
	END

RESULT:
Check-View-Existence-using-Object_Id function 2

Approach 5: Avoid Using sys.sysobjects System table

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog views sys.objects/sys.views/sys.sql_modules instead of sys.sysobjects system table directly to check the existence of the View.

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.sysobjects  
     WHERE id = OBJECT_ID(N'dbo.vwGetCustomerInfo') 
                      AND xtype=N'V')
	BEGIN
		PRINT 'View Exists'
	END

RESULT:
Check-View-Existence-using-sys.sysobjects

[ALSO READ] :
How to check if a Database exists
How to check if a Table exists
How to check if a Stored Procedure exists in Sql Server
How to check if Temp table exists
How to check if a record exists in table

Looping through table records in Sql Server

This article lists out extensive list of example scripts for looping through table records one row at a time. This article covers the examples for the following scenario’s for looping through table rows

  1. Looping column having no gaps/duplicate values
  2. Looping column having gaps
  3. Looping column having duplicates

[ALSO READ] WHILE loop in Sql Server

To understand the looping of the table records in the above listed scenarios, let us first create a temporary table #Employee as shown in the below image with sample data using the following script.

WHILE Loop Example Sql Server
Script:

USE TEMPDB
GO
CREATE TABLE #Employee
(Id INT, Name NVARCHAR(100), Status TINYINT)
GO
INSERT INTO #Employee ( Id, Name, Status)
Values (1, 'Basavaraj Biradar', 0),
		(2, 'Shree Biradar', 0),
		(3, 'Kalpana Biradar', 0)
GO

The below examples illustrates how we can loop through table records in various ways. And also highlights the problem if any. Please go through all the examples before deciding on using one particular approach.

Example 1: Looping column having no gaps/duplicate values

Approach 1: Looping through table records with static loop counter initialization

DECLARE @LoopCounter INT = 1, @MaxEmployeeId INT = 3 , 
        @EmployeeName NVARCHAR(100)

WHILE(@LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name 
   FROM #Employee WHERE Id = @LoopCounter

   PRINT @EmployeeName	
   SET @LoopCounter  = @LoopCounter  + 1 	    
END

RESULT:
Looping through table records Sql Server 1

In this example the loop running variable @LoopCounter and the maximum loop counter variable @MaxEmployeeId values are initialized with a static value.

Note: This approach of looping through table rows doesn’t work if the looping column (i.e. in this case Id column of the #Employee table) values have gaps or if it has duplicate values

Approach 2: Looping through table records with dynamic loop counter initialization

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee

WHILE(@LoopCounter IS NOT NULL 
      AND @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name 
   FROM #Employee WHERE Id = @LoopCounter
   
   PRINT @EmployeeName	
   SET @LoopCounter  = @LoopCounter  + 1 	    
END

RESULT:
Looping through table records Sql Server 2

In this example the loop running variable @LoopCounter and the maximum loop counter variable @MaxEmployeeId values are initialized dynamically.

Note: This approach of looping through table rows doesn’t work if the looping column (i.e. in this case Id column of the #Employee table) values have gaps or if it has duplicate values

Example 2: Looping through table records where looping column has gaps

Issue with example 1’s approach 1 and 2: These example approaches are assuming that looping column values doesn’t have any gap in it. Let us see what is the output of the example 1’s approach 1 and 2 if we have gaps in the looping column value.

To create a gap, delete employee record from the #Employee table with id = 2 by the following script:

DELETE FROM #EMPLOYEE WHERE Id = 2

RESULT:
Looping through table records Sql Server 3

Now let us run the example 1’s approach 1 and 2 script on #Employee table which is having gap in the Id column value (i.e. record with id column value 2 is missing).

Looping through table records Sql Server 12

From the above result it is clear that the example 1’s approach 1 and 2 script will not work in the scenarios where we have gap in the looping tables column values.

This problem can solved in multiple ways, below are two such example approaches. I would prefer the first approach.

Approach 1: Looping through table records where looping column has gaps in the value

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee

WHILE ( @LoopCounter IS NOT NULL 
        AND  @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name FROM #Employee 
   WHERE Id = @LoopCounter
   PRINT @EmployeeName	
   SELECT @LoopCounter  = min(id) FROM #Employee
   WHERE Id > @LoopCounter
END

RESULT:
Looping through table records Sql Server 6

From the above result it is clear that this script works even when we have gaps in the looping column values.

Note: This approach of looping through table rows doesn’t work if the looping column (i.e. in this case Id column of the #Employee table) has duplicate values

Approach 2: Looping through table records where looping column has gaps in the value

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee
WHILE ( @LoopCounter IS NOT NULL 
        AND  @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name 
   FROM #Employee WHERE Id = @LoopCounter
   --To handle gaps in the looping column value
   IF(@@ROWCOUNT = 0 )
   BEGIN
	 SET @LoopCounter  = @LoopCounter  + 1 
	 CONTINUE
   END

   PRINT @EmployeeName	
   SET @LoopCounter  = @LoopCounter  + 1 	    
END

Looping through table records Sql Server 7

From the above result it is clear that this script works even when we have gaps in the looping column values.

Note: This approach of looping through table rows doesn’t work if the looping column (i.e. in this case Id column of the #Employee table) has duplicate values

Example 3: Looping through table records where looping column having duplicates

To create a duplicate record, insert one more employee record to the #Employee table with id = 1 by the following script:

INSERT INTO #Employee ( Id, Name, Status)
Values (1, 'Sharan Biradar', 0)

RESULT:
Looping through table records Sql Server 8

Now let us run the example 2’s approach 1 and 2 script on #Employee table which is having duplicate Id column values (i.e. there are two records with with Id column value as 1)

Looping through table records Sql Server 13

From the above result it is clear that the example 2’s approach 1 and 2 script will not work in the scenarios where we have duplicates in the looping column. Here only one record of the employee with id =1 is displayed and other record is skipped. This problem can solved in multiple ways, below are two such example approaches.

Approach 1: Looping through table records where looping column has duplicate values

SET NOCOUNT ON
DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(id) , @MaxEmployeeId = max(Id) 
FROM #Employee
 
WHILE  ( @LoopCounter IS NOT NULL
         AND  @LoopCounter <= @MaxEmployeeId)
BEGIN
   UPDATE TOP(1) #Employee
   SET  Status = 1, @EmployeeName = Name
   WHERE Id = @LoopCounter  AND Status = 0 
 
   PRINT @EmployeeName  
 
   SELECT @LoopCounter  = min(id) FROM #Employee  
   WHERE Id >= @LoopCounter AND Status = 0
END

RESULT:
Looping through table records Sql Server 10

In this approach using the Status column to mark the records which are already processed. And also the update statement is used to update the status and also get the row values and one more thing is in Update using the TOP statement to update only one record at a time.

Approach 2: Looping through table records where looping column has duplicate values by inserting records into another temp table with identity column

--Create another temp table with identity column
CREATE TABLE #EmployeeCopy (LoopId INT IDENTITY(1,1), 
  Id INT, Name NVARCHAR(100), Status TINYINT)
--Copy data to the table with identity column
INSERT INTO #EmployeeCopy(Id, Name, Status)
SELECT Id, Name, Status FROM #Employee 

DECLARE @LoopCounter INT , @MaxEmployeeId INT, 
        @EmployeeName NVARCHAR(100)
SELECT @LoopCounter = min(LoopId),@MaxEmployeeId = max(LoopId) 
FROM #EmployeeCopy
WHILE ( @LoopCounter IS NOT NULL 
        AND  @LoopCounter <= @MaxEmployeeId)
BEGIN
   SELECT @EmployeeName = Name 
   FROM #EmployeeCopy  WHERE LoopId = @LoopCounter
   PRINT @EmployeeName	
   SELECT @LoopCounter  = min(LoopId) 
   FROM #EmployeeCopy  WHERE LoopId > @LoopCounter
END

RESULT:
Looping through table records Sql Server 11

In this article I have covered most of the basic scenarios which we across. If you have any other scenario and use different approach, post a comment I will update the article.

[ALSO READ] PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server

Truncate all/all except few/specified Tables of a Database in Sql Server

This article presents how we can generate a script to truncate all tables/all tables except few specified tables/specified tables of a Database in Sql Server.

One of major the problem with table truncation is, we need to remove if there are any foreign key’s defined in other tables which references the columns in the table to be truncated. So to truncate a table we need to first remove all the foreign key references then truncate the table and finally add back the removed foreign key constraints.

Let us first create a demo database with sample tables as shown in the below image by the by the following script:

TruncateAllDemoDB

--Create demo database with tables having foreign
--key relations between them and sample data in it
SET NOCOUNT ON
GO
CREATE DATABASE SqlhintsTruncateDBDemo
GO
USE SqlhintsTruncateDBDemo
GO
CREATE TABLE [dbo].[Customer] (
    [CustID] [int] NOT NULL ,
    CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
    ( [CustID] ) 
) 
GO
CREATE TABLE [dbo].[Order] (
    [OrderID] [int] NOT NULL ,
    [CustID] [int] NOT NULL ,
    CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
    ( [OrderID] ),
    CONSTRAINT [FK_Order_Customer] FOREIGN KEY
    ( [CustID] ) REFERENCES [dbo].[Customer] (CustID) 
) 
GO
CREATE TABLE [dbo].[OrderItem] (
    [OrderItemId] [int] NOT NULL ,
    [OrderId] [int] NOT NULL ,
    CONSTRAINT [PK_OrderItem] PRIMARY KEY  CLUSTERED 
    ( [OrderItemId] ),
    CONSTRAINT [FK_OrderItem_Order] FOREIGN KEY
    ([OrderId]) REFERENCES [dbo].[Order] ([OrderId]) 
) 
GO

We can use the below script to generate the script to truncate all the tables of a database.

USE SqlhintsTruncateDBDemo
GO
SET NOCOUNT ON
GO
--Get the list of all the tables to be truncated
 DECLARE @TablesToBeTruncated AS TABLE
 (Id INT IDENTITY(1,1),TableObjectId INT, TableName SYSNAME,
	SchemaId INT)
INSERT INTO @TablesToBeTruncated
 SELECT ST.object_id,ST.name,ST.schema_id
 FROM sys.Tables ST
 WHERE ST.type = 'U' AND ST.NAME NOT LIKE '#%' 
 AND ST.name <> 'sysdiagrams'
 --AND ST.NAME NOT IN ('') -- Specify here the comma separated table names for which truncation is not required
 --AND ST.NAME IN ('') -- Specify here the comma separated table names which needs to be truncated

 --Generate the foreignkeys drop and create back script 
DECLARE @CreateScript AS NVARCHAR(MAX), @DropScript AS NVARCHAR(MAX)
SELECT 
	------------DROP SCRIPT--------------------
	@DropScript = ISNULL(@DropScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
	 + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(FKey.name)
	 + CHAR(10),
	 -----------CREATE BACK SCRIPT-------------
	@CreateScript = ISNULL(@CreateScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
	 + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' ADD CONSTRAINT ' + QUOTENAME(FKey.name)
	 + ' FOREIGN KEY ' + '(' + STUFF(( -- Get the list of columns
				 SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.parent_object_id, FKeyCol.parent_column_id))
				 FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
				 WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
				 ORDER BY FKeyCol.constraint_column_id
				 FOR XML PATH('')),1,1,'') + ')'
	 + ' REFERENCES ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.' 
				+ QUOTENAME(OBJECT_NAME(FKey.referenced_object_id)) + ' (' + STUFF(( -- Get the list of columns
				SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.referenced_object_id, FKeyCol.referenced_column_id))
				FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
				WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
				ORDER BY FKeyCol.constraint_column_id
				FOR XML PATH('')),1,1,'') + ') '
	 + CASE WHEN update_referential_action_desc = 'CASCADE' THEN ' ON UPDATE CASCADE'
	        WHEN update_referential_action_desc = 'SET_DEFAULT' THEN ' ON UPDATE SET DEFAULT'
	        WHEN update_referential_action_desc = 'SET_NULL' THEN ' ON UPDATE SET NULL'
	        ELSE '' 
	   END
	 + CASE WHEN delete_referential_action_desc = 'CASCADE' THEN ' ON DELETE CASCADE'
			WHEN delete_referential_action_desc = 'SET_DEFAULT' THEN ' ON DELETE SET DEFAULT'
			WHEN delete_referential_action_desc = 'SET_NULL' THEN ' ON DELETE SET NULL'
			ELSE ''
	   END  + CHAR(10)
 FROM @TablesToBeTruncated Tlist
			INNER JOIN SYS.FOREIGN_KEYS FKey
				ON Tlist.TableObjectId = FKey.referenced_object_id

--PRINT THE TRUNCATION SCRIPT
IF LEN(ISNULL(@DropScript,'')) > 0
 BEGIN
	 PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------DROP FOREIGN KEY CONSTRAINTS SCRIPT--------'
	 PRINT @DropScript + CHAR(10) + ' GO ' + CHAR(10)
 END

PRINT '--------TRUNCATE TABLES SCRIPT--------'
--TRUNCATE TABLES
DECLARE @id INT,@truncatescript NVARCHAR(MAX)
SELECT @id = MIN(Id)FROM @TablesToBeTruncated
WHILE @id is not null
 BEGIN
	 SELECT @truncatescript = 'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(SchemaId)) + '.' + QUOTENAME(TableName) 
	 FROM @TablesToBeTruncated WHERE Id = @id
	 PRINT @truncatescript
	 SELECT @id = MIN(Id)FROM @TablesToBeTruncated WHERE Id > @id
 END

IF LEN(ISNULL(@CreateScript,'')) > 0
 BEGIN
	 PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------CREATE BACK THE FOREIGN KEY CONSTRAINTS SCRIPT--------'
	 PRINT CAST((@CreateScript + CHAR(10) + ' GO ' + CHAR(10)) AS NTEXT)
 END
 GO

Below is the result of executing the above script:

TruncateAllTableDataResult

Disclaimer: As TRUNCATE table removes all the records from the table permanently. So, think twice before executing the truncate table statement.

By default the above script generates the script to truncate all the tables of a database. If need is to truncate only the specified tables then uncomment the line number 15 (i.e.–AND ST.NAME IN (”)) in the above script and mention the list of tables to be truncated. And if requirement is to truncate all the tables except few tables, then uncomment only the line number 14 (i.e. –AND ST.NAME NOT IN (”)) in the above script and mention the list of tables which shouldn’t be considered for truncation.

Cannot truncate table ‘xyz’ because it is being referenced by a FOREIGN KEY constraint – Sql Server

In this article we will discuss on when we get the error like below in Sql Server and how to resolve it.

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘Customer’ because it is being referenced by a FOREIGN KEY constraint.

To demonstrate this error let us first create a demo db ‘SqlhintsTruncateDemo’, two tables Customer and Order.

CREATE DATABASE SqlhintsTruncateDemo
GO
USE SqlhintsTruncateDemo
GO
CREATE TABLE [dbo].[Customer] (
	[CustID] [int] IDENTITY (1, 1) NOT NULL ,
	CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
	(
		[CustID]
	) 
) 
GO
CREATE TABLE [dbo].[Order] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustID] [int] NOT NULL ,
	CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	),
	CONSTRAINT [FK_Order_Customer] FOREIGN KEY 
	(
		[CustID]
	) REFERENCES [dbo].[Customer] (
		[CustID]
	) 
) 
GO

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

In the above script we have created a foreign key constraint FK_Order_Customer on the CustID column of the Order table which is referring to the CustID primary key column of the Customer table.

Disclaimer: As TRUNCATE table removes all the records from the table. Be careful before issuing this command.

Now try to truncate the Order Table

TRUNCATE TABLE [dbo].[Order]

RESULT:
Truncate Table Successful

As per the above result truncation of the Order table is successful. Now try to truncate the Customer Table

TRUNCATE TABLE [dbo].[Customer]

RESULT:
Truncate Table Failure

As per the result the truncation of the Customer table is failing, because the
CustID column of the Customer table is referenced by the CustID column of the Order Table.

If we still want to Truncate the table, then we have to drop all the foreign key
constraints which are referring to the table to be truncated.

So now drop the foreign key constraint FK_Order_Customer and then try truncating
the Customer table.

ALTER TABLE [dbo].[Order]
DROP CONSTRAINT FK_Order_Customer
GO
TRUNCATE TABLE [dbo].[Customer]
GO

RESULT:
Truncate Table Successful After Dropping of Foreign Key Constraint

As per the above result it is clear that now the table truncation is successful after dropping all the foreign key constraints which are refering to the table to be truncated.

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server.

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
OBJECT_NAME(FK.parent_object_id) 'Referring Table',
FK.name 'Foreign Key',
COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
INNER JOIN sys.foreign_key_columns AS FKC
ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'

In the above script replace the string ‘Enter Table Name’ with the table name for which you want to find out the referencing tables and the refering foreign key constraint name.

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

How to check if Temp table exists in Sql Server?

This article shows how in Sql Server we can check the existence of Temporary Table.

To demonstrate this let us first create a Temporary Table with name #TempTable.

--Create Temporary Table
 CREATE TABLE #TempTable (Id INT)
 GO

Below script shows how we can check the existence of a Temporary Table #TempTable.

IF OBJECT_ID('TempDB.dbo.#TempTable') IS NOT NULL
BEGIN
  PRINT '#TempTable Temporary Table Exists'
END
GO

RESULT:
How to Check if Temporary Table exists in Sql Server

Note: As shown above to check the existence of a temporary table, we need give table name with three part naming convention i.e. DatabaseName.SchemaName.TemporaryTableName

[ALSO READ] :
How to check if a Database exists
How to check if a Table exists
How to check if a Stored Procedure exists in Sql Server
How to check if a View exists
How to check if a record exists in table