USE MASTER
GO
IF OBJECT_ID('sp_Table_Script') IS NULL
EXEC ('CREATE PROCEDURE sp_Table_Script AS SELECT 1 AS ID')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE sp_Table_Script
@ObjectName SYSNAME = NULL, /* NULL for all, supports like */
@SchemaName SYSNAME = NULL, /* NULL for all, supports like */
@ObjectList XML = NULL, /* Will overwrite @ObjectName */
@NewSchemaName SYSNAME = NULL, /* NULL to scripts objects in original schema, any value overwrites */
@NewTableName SYSNAME = NULL, /* This can be used if only one table is scripted, it will change the table name */
@NewDatabaseName SYSNAME = NULL, /* To change the database context */
@NewFileGroup SYSNAME = NULL, /* A new filegroup for all objects created can be given or a full partition schema with column) */
@NewPartition SYSNAME = NULL, /* Full partition clause with the parameter like 'psDatasupplierIdTrans(DataSupplierId)' */
@NewDataCompression SYSNAME = NULL, /* A blank value will not script datacompression options, any value will create all objects with the new data compression */
@AddNamePrefix SYSNAME = NULL, /* Adds a prefix to table and constraint names being created */
@ReplaceReferenceSchemaFrom SYSNAME = NULL, /* ** For foreign key constrainst you can replace a referenced schema name @ReplaceReferenceSchemaFrom as @ReplaceReferenceSchemaTo */
@ReplaceReferenceSchemaTo SYSNAME = NULL, /* ** Used in conjunction with @ReplaceReferenceSchemaFrom*/
@DropForeignKey BIT = 0,
@DropTable BIT = 0,
@DropDefaultConstraints BIT = 0, /* This will drop default constraints */
@DropClusteredIndex BIT = 0,
@DropNonClusteredIndex BIT = 0,
@DropPrimaryKey BIT = 0,
@DropUniqueKey BIT = 0,
@DropIndex BIT = 0,
@DropCheckConstraint BIT = 0,
@DropFunction BIT = 0,
@DropProcedure BIT = 0,
@DropTrigger BIT = 0,
@DropView BIT = 0,
@CreateTable BIT = 1, /* Table will always be created with it's defaults */
@CreateTableInOneStep BIT = 0,
@IncludeSchemaBoundObjects BIT = 1,
@CreateDefaultConstraints BIT = 1, /* This will create default constraints */
@EnableNonClusteredIndex BIT = 0,
@EnableUniqueKey BIT = 0,
@EnableIndex BIT = 0,
@DisableNonClusteredIndex BIT = 0,
@DisableUniqueKey BIT = 0,
@DisableIndex BIT = 0,
@CreateClusteredIndex BIT = 0,
@CreateNonClusteredIndex BIT = 0,
@CreatePrimaryKey BIT = 1,
@CreateUniqueKey BIT = 1,
@CreateIndex BIT = 1,
@CreateForeignKey BIT = 1,
@CreateForeignKeyInTwoSteps BIT = 0,
@CreateCheckConstraint BIT = 1,
@CreateCheckConstraintInTwoSteps BIT = 0,
@CreateFunction BIT = 0,
@CreateProcedure BIT = 0,
@CreateTrigger BIT = 1,
@CreateView BIT = 0,
@AlterProcedures BIT = 0,
@CreateSETANSI BIT = 0,
@IgnoreDisabledForeignKey BIT = 0,
@IgnoreDisabledCheckConstraint BIT = 0,
@SortInTempDb BIT = 1,
@SortTableColumns BIT = 0,
@GenerateIfNotExists BIT = 0,
@UseGOBatchTerminator BIT = 1,
@IncludeMSShipped BIT = 0,
@ExcludeSchemaList VARCHAR(MAX) = 'cdc,sys',
@ExcludeSchemaDelimiter VARCHAR(10) = ',',
@SQL VARCHAR(MAX) = NULL OUTPUT, /* This will append to the passed value */
@PrintSQL BIT = 1,
@ReturnRecordset BIT = 0,
@ExecuteSQL BIT = 0, /* Executes the generated sql statement */
@UseTransaction BIT = 0, /* SQL script will be executed within a transaction */
@RtrimLines BIT = 1,
@UseObjectIdForDrop BIT = 1,
@recipients NVARCHAR(1000) = NULL,
@subject NVARCHAR(1000) = NULL,
@FilePath VARCHAR(4000) = NULL /* Will write the generated sql to the path */
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ReturnVal INT
EXEC @ReturnVal = dbo.sp_script
@ObjectName = @ObjectName,
@SchemaName = @SchemaName,
@ObjectList = @ObjectList,
@NewSchemaName = @NewSchemaName,
@NewTableName = @NewTableName,
@NewDatabaseName = @NewDatabaseName,
@NewFileGroup = @NewFileGroup,
@NewPartition = @NewPartition,
@NewDataCompression = @NewDataCompression,
@AddNamePrefix = @AddNamePrefix,
@ReplaceReferenceSchemaFrom = @ReplaceReferenceSchemaFrom,
@ReplaceReferenceSchemaTo = @ReplaceReferenceSchemaTo,
@DropForeignKey = @DropForeignKey,
@DropTable = @DropTable,
@DropDefaultConstraints = @DropDefaultConstraints,
@DropClusteredIndex = @DropClusteredIndex,
@DropNonClusteredIndex = @DropNonClusteredIndex,
@DropPrimaryKey = @DropPrimaryKey,
@DropUniqueKey = @DropUniqueKey,
@DropIndex = @DropIndex,
@DropCheckConstraint = @DropCheckConstraint,
@DropFunction = @DropFunction,
@DropProcedure = @DropProcedure,
@DropTrigger = @DropTrigger,
@DropView = @DropView,
@CreateTable = @CreateTable,
@CreateTableInOneStep = @CreateTableInOneStep,
@IncludeSchemaBoundObjects = @IncludeSchemaBoundObjects,
@CreateDefaultConstraints = @CreateDefaultConstraints,
@EnableNonClusteredIndex = @EnableNonClusteredIndex,
@EnableUniqueKey = @EnableUniqueKey,
@EnableIndex = @EnableIndex,
@DisableNonClusteredIndex = @DisableNonClusteredIndex,
@DisableUniqueKey = @DisableUniqueKey,
@DisableIndex = @DisableIndex,
@CreateClusteredIndex = @CreateClusteredIndex,
@CreateNonClusteredIndex = @CreateNonClusteredIndex,
@CreatePrimaryKey = @CreatePrimaryKey,
@CreateUniqueKey = @CreateUniqueKey,
@CreateIndex = @CreateIndex,
@CreateForeignKey = @CreateForeignKey,
@CreateForeignKeyInTwoSteps = @CreateForeignKeyInTwoSteps,
@CreateCheckConstraint = @CreateCheckConstraint,
@CreateCheckConstraintInTwoSteps = @CreateCheckConstraintInTwoSteps,
@CreateFunction = @CreateFunction,
@CreateProcedure = @CreateProcedure,
@CreateTrigger = @CreateTrigger,
@CreateView = @CreateView,
@AlterProcedures = @AlterProcedures,
@CreateSETANSI = @CreateSETANSI,
@IgnoreDisabledForeignKey = @IgnoreDisabledForeignKey,
@IgnoreDisabledCheckConstraint = @IgnoreDisabledCheckConstraint,
@SortInTempDb = @SortInTempDb,
@SortTableColumns = @SortTableColumns,
@GenerateIfNotExists = @GenerateIfNotExists,
@UseGOBatchTerminator = @UseGOBatchTerminator,
@IncludeMSShipped = @IncludeMSShipped,
@ExcludeSchemaList = @ExcludeSchemaList,
@ExcludeSchemaDelimiter = @ExcludeSchemaDelimiter,
@SQL = @SQL OUTPUT,
@PrintSQL = @PrintSQL,
@ReturnRecordset = @ReturnRecordset,
@ExecuteSQL = @ExecuteSQL,
@UseTransaction = @UseTransaction,
@RtrimLines = @RtrimLines,
@UseObjectIdForDrop = @UseObjectIdForDrop,
@recipients = @recipients,
@subject = @subject,
@FilePath = @FilePath
RETURN @ReturnVal
GO
EXEC sys.sp_ms_marksystemobject
sp_Table_Script
GO