Using SSMS Templates

SQL Server Management Studio (SSMS) has an oft-overlooked gem called "Templates," which are simply parameterized SQL scripts for common tasks. Having (and maintaining) a personal Template "toolbox" can save you hours of work, and help you create a standard set of debugged, error-checking, exception-handling, idempotent, and commented T-SQL code for both DML and DDL. For a tutorial, see my blog post Using SSMS Templates for Fun and Profit!

My Template Toolbox

Here is my personal "toolbox" of SSMS Templates. The naming convention is "noun_verb_details", for example, "Statistics_Drop_OnTable_00.sql" which makes it much easier to find them. The two digit number at the end represents the version of SQL Server the script was originally written for. As you know, most of what works in SQL Server 2000 still works in SQL Server 2008 - but some things don't, so caveat coder.




Contents of LarryLeonardSsmsTemplates.zip

    ######## Where These Go.txt

    ##ScriptTemplate.sql

    ##TemplateVariables.sql

    @@ERROR Handler_00.sql

    @MyErrDeclaration_00.sql

    Column_Add_BIGINT_IDENTITY_PRIMARYKEY_CLUSTERED_05.sql

    Column_Add_BIGINT_UNIQUE_05.sql

    Column_Add_COMPUTED_CHECK_05.sql

    Column_Add_DATETIME_05.sql

    Column_Add_Generic_05.sql

    Column_Add_GUID_DEFAULT_05.sql

    Column_Add_IDENTITY_PRIMARYKEY_CLUSTERED_05.sql

    Column_Add_INT_DEFAULT_CHECK_05.sql

    Column_Add_INT_DEFAULT_FOREIGNKEY_05.sql

    Column_Add_INT_FOREIGNKEY_05.sql

    Column_Add_INT_IDENTITY_PRIMARYKEY_CLUSTERED_05.sql

    Column_Add_NVARCHAR_05.sql

    Column_Add_NVARCHAR_DEFAULT_05.sql

    Column_Add_SMALLDATETIME_05.sql

    Column_Add_SMALLDATETIME_DEFAULT_05.sql

    Column_Add_SMALLDATETIME_DF_CK_05.sql

    Column_Add_SMALLINT_DEFAULT_05.sql

    Column_Add_TINYINT_DEFAULT_CHECK_05.sql

    Column_Add_To_Table_00.sql

    Column_Change_To_NOT_NULL_00.sql

    Column_Change_Type_05.sql

    Column_Drop_05.sql

    Column_Exists_00.sql

    Column_Exists_05.sql

    Column_Is_AlterTable-able_00.sql

    Column_Is_InPrimaryKey_00.sql

    Column_Is_NOT_NULL_00.sql

    Column_Rename_05.sql

    Constraint_Add_CK_00.sql

    Constraint_Add_CK_05.sql

    Constraint_Add_Clustered_05.sql

    Constraint_Add_Clustered_08.sql

    Constraint_Add_DF_00.sql

    Constraint_Add_DF_05.sql

    Constraint_Add_FK_05.sql

    Constraint_Add_PK_00.sql

    Constraint_Add_PK_Clustered_05.sql

    Constraint_Add_PK_NonClustered_05.sql

    Constraint_Add_UQ_NonClustered_05.sql

    Constraint_Disable_05.sql

    Constraint_Drop_CK_05.sql

    Constraint_Drop_DF_00.sql

    Constraint_Drop_DF_05.sql

    Constraint_Drop_DF_Any_Name_05.sql

    Constraint_Drop_FK_05.sql

    Constraint_Drop_PK_05.sql

    Constraint_Exists_00.sql

    Constraint_Exists_DF_05.sql

    Constraint_Exists_FK_05.sql

    Constraint_Exists_PK_05.sql

    Cursor_DeclareUseDeallocate_05.sql

    Cursor_Drop_00.sql

    Cursor_Drop_05.sql

    Cursor_Exists_00.sql

    Cursor_Exists_05.sql

    Database_Create_00.sql

    Database_Exists_00.sql

    Database_Exists_05.sql

    ErrorHandling_CreateStoredProc_05.sql

    ErrorHandling_TryCatchBlock_05.sql

    ExtendedProperty_Column_00.sql

    ExtendedProperty_Column_05.sql

    ExtendedProperty_Column_GUID_05.sql

    ExtendedProperty_Constraint_CK_05.sql

    ExtendedProperty_Constraint_DF_05.sql

    ExtendedProperty_Constraint_DF_GUID_05.sql

    ExtendedProperty_Constraint_FK_05.sql

    ExtendedProperty_Constraint_PK_05.sql

    ExtendedProperty_Exists_05.sql

    ExtendedProperty_Function_05.sql

    ExtendedProperty_Index_00.sql

    ExtendedProperty_Index_05.sql

    ExtendedProperty_Index_Clustered_05.sql

    ExtendedProperty_Index_For_FK_05.sql

    ExtendedProperty_Index_for_UQ_05.sql

    ExtendedProperty_Procedure_05.sql

    ExtendedProperty_Table_00.sql

    ExtendedProperty_Table_05.sql

    File_Exists_05.sql

    Function_Drop_05.sql

    Function_Exists_05.sql

    Function_Scalar_Create_00.sql

    Function_Scalar_Create_05.sql

    Index_Add_NonClustered_00.sql

    Index_Add_NonClustered_05.sql

    Index_Add_NonClustered_Included_05.sql

    Index_Create_Clustered_00.sql

    Index_Create_Clustered_UQ_05.sql

    Index_Drop_00.sql

    Index_Drop_Either_05.sql

    Index_Drop_NonClustered_05.sql

    Index_Drop_NonClustered_for_FK_05.sql

    Index_Exists_00.sql

    Index_Exists_Clustered_00.sql

    Index_Exists_Clustered_05.sql

    Index_Exists_Either_05.sql

    Index_Exists_NonClustered_05.sql

    Insert_OutputTo_ Insert.sql

    Login_SQL_Exists_05.sql

    RAISERROR_10_00.sql

    RAISERROR_10_Simple_00.sql

    RAISERROR_10_Simple_05.sql

    SetContextInfo_00.sql

    sp_msForEachDb_UsingArgs_00.sql

    sp_msForEachDb_UsingStatement_00.sql

    Statistics_Create_OnTable_00.sql

    Statistics_Drop_OnTable_00.sql

    Statistics_Update_OnIndex.sql

    StoredProcedure_Create_05.sql

    StoredProcedure_Drop_00.sql

    StoredProcedure_Drop_05.sql

    StoredProcedure_Exists_00.sql

    StoredProcedure_Exists_05.sql

    Table_Create_00.sql

    Table_Create_05.sql

    Table_Drop_00.sql

    Table_Drop_05.sql

    Table_Exists_00.sql

    Table_Exists_05.sql

    Table_IsReferencedBy_ForeignKeys.sql

    TempTable_Create_ForSpSpaceUsed_00.sql

    TempTable_Exists_00.sql

    TriggerDDL_Table_Drop_05.sql

    Trigger_Add_00.sql

    Trigger_Add_05.sql

    Trigger_Create_00.sql

    Trigger_Disable_05.sql

    Trigger_Drop_05.sql

    Trigger_Enable_05.sql

    Trigger_Exists_05.sql

    Type_Create_05.sql

    Type_Exists_05.sql

    View_Create_05.sql

    View_Drop_05.sql

    View_Exists_05.sql

    xp_regwrite_00.sql


Licensing

Like everything in this blog, this ZIP file is licensed under the Creative Commons - Attribution license, which (and this is my interpretation) allows you to copy, distribute, remix, tweak, and build upon this code, even for commercial use, as long as you credit me for the original creation, by at least including the line of code that contains my name. According to Creative Commons, "This is the most accommodating of licenses offered, in terms of what others can do with your works[.]"