I'm putting this TRY CATCH template on here in case other people might find it helpful. This is what I usually use when wrapping SQL Scripts in a stored procedure. Normally I would have go to Microsoft Docs to get the syntax for using TRY CATCH and RAISERROR. Having a template that I know works and is ready to go is just easier.
BEGIN TRY
BEGIN TRAN
-- Add your scripts in here
COMMIT TRAN
END TRY
BEGIN CATCH
WHILE (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRAN;
END
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
The scripts below will help you find a table in a SQL Server database based on a given “table name” filter. This is helpful if you are not familiar with a rather large database and didn't want to waste time looking for a table manually. In my case, I usually use these scripts to check for an existing table in a database, before I create a new one.
Most of the time when working with a large database, the table that you think you need to add, was already created by someone else. Oftentimes it is named differently than what you would have named it, but the contents and use of the table would have been the same. So it pays to double check before creating an already existing table.
SELECT *
FROM SYS.tables
WHERE NAME LIKE '%tableName%'
/* The script below will give you a result in the "schema.tablename" format, which can help you quickly locate the table. */
SELECT (SELECT [Name] FROM SYS.schemas AS S WHERE S.schema_id = T.schema_id) + '.' + T.Name AS 'TableName'
FROM SYS.tables AS T
WHERE NAME LIKE '%tableName%'