Some useful tips and tricks in SQL

SQL_Helpful_Scripts.md

Duplicate a database within same SQL server

Firstly, backup your exist database which you want to copy with.

backup database <ExistDatabaseName>
to disk='D:\MSSQL\Backup\ExistDatabaseName.bak';

Then after you should check exist database's logical file names before restore.

RESTORE FILELISTONLY   
FROM DISK= 'D:\MSSQL\Backup\ExistDatabaseName.bak'

Secondly, restore your exist database and give a name for a copy

restore database <NewDatabaseName>
from disk='D:\MSSQL\Backup\ExistDatabaseName.bak'
WITH 
move 'ExistDatabaseName' to  'D:\MSSQL\DATA\NewDatabaseName.mdf',
move 'ExistDatabaseName_Log' to  'D:\MSSQL\DATA\NewDatabaseName_Log.ldf';
GO

Then the exist database is copied to new database. The MOVE statement causes the data and log file to be restored to the specified locations. You do not need to create database with giving a name and the script would create it.

How can i get the list of tables in the stored procedure

;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d 
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

https://stackoverflow.com/a/16229560

Find where specific stored procedure is used in SQL Server database

select schema_name(o.schema_id) + '.' + o.name as [procedure],
       'is used by' as ref,
       schema_name(ref_o.schema_id) + '.' + ref_o.name as [object],
       ref_o.type_desc as object_type
from sys.objects o
join sys.sql_expression_dependencies dep
     on o.object_id = dep.referenced_id
join sys.objects ref_o
     on dep.referencing_id = ref_o.object_id
where o.type in ('P', 'X')
      and schema_name(o.schema_id) = 'dbo'  -- put schema name here
      and o.name = 'uspPrintError'  -- put procedure name here
order by [object];

https://dataedo.com/kb/query/sql-server/find-where-specific-stored-procedure-is-used