Query all Stored Procedure in SQL

This will be useful if you want to list down all Stored Procedure names in a database.


SELECT
*
FROM DatabaseName.information_schema.routines
WHERE routine_type = ‘PROCEDURE’
ORDER BY SPECIFIC_NAME ASC;

If you want to filter the list of SP (not including those with sp_, xp_, dt_, ms_ etc…)


SELECT
SPECIFIC_NAME,
‘sp_helptext ‘ + SPECIFIC_NAME AS ‘QueryStmt’
FROM Forex.information_schema.routines
WHERE routine_type = ‘PROCEDURE’
AND LEFT(Routine_Name, 3) NOT IN (‘sp_’, ‘xp_’, ‘ms_’, ‘dt_’)
ORDER BY SPECIFIC_NAME ASC;

Create table backup in SQL

There are times that we need to play around the table in our database. But before mess it up, better to backup it first.

Usually, this is how i do it before:

1. Use the auto generated script to create the main table.
2. Update the script and rename it with backup table name. Execute the script.
3. Create insert script to insert all records from main to backup table.

I wasn’t aware that there was a short way to do this, just executing this script

SELECT
     * INTO
dbo.BackupTableName
FROM dbo.TableName;



One day, one script at a time 🙂