Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

 In this article, I am explaining how to Drop all the tables, stored procedures, triggers, constraints, and all dependencies in one SQL statement.

/* Drop all non-system stored procs */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

 

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null

BEGIN

    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'

    EXEC (@SQL)

    PRINT 'Dropped Procedure: ' + @name

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

 

/* Drop all views */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

 

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

 

WHILE @name IS NOT NULL

BEGIN

    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'

    EXEC (@SQL)

    PRINT 'Dropped View: ' + @name

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

 

/* Drop all functions */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

 

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

 

WHILE @name IS NOT NULL

BEGIN

    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'

    EXEC (@SQL)

    PRINT 'Dropped Function: ' + @name

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

 

/* Drop all Foreign Key constraints */

DECLARE @name VARCHAR(128)

DECLARE @constraint VARCHAR(254)

DECLARE @SQL VARCHAR(254)

 

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

 

WHILE @name is not null

BEGIN

    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

    WHILE @constraint IS NOT NULL

    BEGIN

        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'

        EXEC (@SQL)

        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

    END

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

END

GO

 

/* Drop all Primary Key constraints */

DECLARE @name VARCHAR(128)

DECLARE @constraint VARCHAR(254)

DECLARE @SQL VARCHAR(254)

 

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

 

WHILE @name IS NOT NULL

BEGIN

    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

    WHILE @constraint is not null

    BEGIN

        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'

        EXEC (@SQL)

        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

    END

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

END

GO

 

/* Drop all tables */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

 

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

 

WHILE @name IS NOT NULL

BEGIN

    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'

    EXEC (@SQL)

    PRINT 'Dropped Table: ' + @name

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

 Ref-https://stackoverflow.com/questions/536350/drop-all-the-tables-stored-procedures-triggers-constraints-and-all-the-depend

SQL SERVER – Find Stored Procedure, View and Function Related to Table in Database

In this article, I am explaining, How to find Stored Procedure, View, and Function list Related to a particular Table in SQL Database. Below query, help to find that

SELECT DISTINCT b.name, b.xtype

FROM syscomments a

INNER JOIN sysobjects b ON a.id = b.id
WHERE a.TEXT LIKE '%tablename%'

If we want to search table only in the stored procedure then we can use the below query

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tablename%'

How to Create a Linked Server In SQL Server

In this article, I am explaining, How to Linked Server In SQL Server.

There are three way to linked server:-

Using SQL Server Management Studio

To create a linked server to another instance of SQL Server Using SQL Server Management Studio

1. In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server.
2. On the General page, in the Linked server box, type the name of the instance of SQL Server that you are linking to.

To create a linked server to another instance of SQL Server using Transact-SQL

In Query Editor, enter the following Transact-SQL command to link to an instance of SQL Server named SRVR002\ACCTG:

SQLCopy

USE [master]
GO

      EXEC master.dbo.sp_addlinkedserver

      @server = N'DOMAIN\SERVER', -- You need to change Server Name

      @srvproduct=N'SQL Server' ;

     GO

Execute the following code to configure the linked server to use the domain credentials of the login that is using the linked server.

SQLCopy

EXEC master.dbo.sp_addlinkedsrvlogin

     @rmtsrvname = N'SERVER NAME', -- YOU need to change the server name

     @locallogin = NULL ,

     @useself = N'True' ;

     GO

Ref- https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15

Currently All running queries in SQL Server

In this article, I am explaining how to find which SQL queries are being run on an SQL Server.

Using the below query, we can get list the queries running on SQL Server

SELECT b.TEXT,

a.session_id,

a.status,

a.command,

a.cpu_time,

a.total_elapsed_time

FROM sys.dm_exec_requests a

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS b

• More info is available  about sys.dm_exec_requests
• More info is available  about sys.dm_exec_sql_text

By using above query, we can find a list of queries. If we want to kill any query. We can use below command-

KILL session_id

How to TRUNCATE ALL TABLES

In this article, I am explaining, How to delete all data or truncate all tables from the database in SQL Server. By using the below queries delete the data, not the structure.

For this, we can use the sp_MSforeachtable stored procedure:

For TRUNCATE  all tables :

USE DatabaseName
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

For delete all data from tables :

USE MyDatabaseName

EXEC sp_MSforeachtable 'DELETE FROM ?'