SQL DB Modification Templates

This post is just a place for me dump some regularly used templates for doing CRUD operations on DB code.

I create these little tidbits at work and then forget to take them with me. So whenever I start a new job, I have to recreate them. This time I’m going to start recording them here.

CREATE OR ALTER PROCEDURE

-- replace values for _schema_, _procname_, _params_, _body_, _user_, and _asuser_. Then delete this line.
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 1/3: Check for existing version starting';
IF OBJECTPROPERTY(OBJECT_ID('[_schema_].[_procname_]'), 'IsProcedure') IS NULL
BEGIN
  PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 1b: Creating stub';
  EXECUTE ('CREATE PROCEDURE [_schema_].[_procname_] AS RAISERROR(''The ALTER PROCEDURE FOR [_schema_].[_procname_] failed'', 16, 1);');
END
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 1/3: Check for existing version completed';

PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 2/3: Altering Stored Procedure starting';
GO
ALTER PROCEDURE [_schema_].[_procname_]
                 _params_
AS
BEGIN
_body_
END; /* [_schema_].[_procname_] */
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 2/3: Altering Stored Procedure completed';
  
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 3/3: Granting Permissions starting';
GRANT EXECUTE
    ON OBJECT::[_schema_].[_procname_] TO [_user_]
    AS [_asuser_];
PRINT CONVERT(varchar,SYSDATETIME(),121)+' ['+DB_NAME()+'].[_schema_].[_procname_] Step 3/3: Granting Permissions completed';
GO

CREATE TEMP TABLE

-- replace values for _temptablename_ and _columns_. Then delete this line.
IF OBJECT_ID('[tempdb]..[#_temptablename_]') IS NOT NULL
    TRUNCATE TABLE [#_temptablename_]
ELSE
    CREATE TABLE #[_temptablename_]
    ( _columns_
    );

DROP TABLE

IF OBJECT_ID(N'dbo._tablename_', N'U') IS NOT NULL  
   DROP TABLE [dbo].[_tablename_];  
GO

There are more to add… when I have to create another, I’ll add it.