Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure to script tables, has many options Expand / Collapse
Author
Message
Posted Monday, January 31, 2011 11:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 20, Visits: 290
Comments posted to this topic are about the item Stored procedure to script tables, has many options
Post #1056608
Posted Tuesday, February 1, 2011 4:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 4, 2011 3:12 AM
Points: 4, Visits: 16
Can u elaborate this clearly. I mean plz. explain how it works and what is the output. Can u give step by step?
Post #1056722
Posted Tuesday, February 1, 2011 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 20, Visits: 290
It will be a lot easier if you were to tell me what do you want to script or create so that I can give you a sample procedure call. The procedure depends on the CLR aggregate posted with the code. By default all options are turned off for proc sp_scriptmain and all create only options and printsql turned on for proc sp_script (which calls sc_scriptmain with turned on options). Parameters have default blank values therefore they are not mandatory. The first parameter is @ObjectName therefore if no parameter name is passed and only one parameter is passed that parameter will be passed into @Objectname.

-- The parameters below is used for filtering tables
@ObjectName SYSNAME = NULL, -- SchemaName.TableName (if this is set the other filters ignored)
@SchemaName SYSNAME = NULL, -- SchemaName (used in like '%test%schema%', it can be used together or not with TableName)
@TableName SYSNAME = NULL, -- TableName(used in like '%test%table%', it can be used together or not with SchemaName)
@TableList XML = NULL, -- xml input for specific tablename + schemaname, check procedure comments

-- This parameters will modify the output table name
@NewSchemaName SYSNAME = NULL,
@NewTableName SYSNAME = NULL,
@NewDatabaseName SYSNAME = NULL,
@NewFileGroup SYSNAME = NULL, -- If Blank no filegroup, if null copy existing, if valid will used instead of
existing
@NewPartition SYSNAME = NULL, -- If Blank no partition scheme, if null copy existing, if valid will used instead of existing
@NewDataCompression SYSNAME = NULL, -- If Blank no compression, if null copy existing, if valid will used instead of existing
@AddNamePrefix SYSNAME = NULL,
@ReplaceReferenceSchemaFrom SYSNAME = NULL, -- if foreign key is using this schema replace it with next schema
@ReplaceReferenceSchemaTo SYSNAME = NULL,

-- Below bitwise fields are self explanatory, if not set the script won't generate them
@DropForeignKey BIT = 0,
@DropTable BIT = 0,
@DropDefaultConstraints BIT = 0,
@DropIndexes BIT = 0,
@DropCheckConstraint BIT = 0,
@CreateTable BIT = 0, /
@CreateDefaultConstraints BIT = 0,
@CreatePrimaryKey BIT = 0,
@CreateUniqueKey BIT = 0,
@CreateIndex BIT = 0,
@CreateForeignKey BIT = 0,
@CreateCheckConstraint BIT = 0,
@IgnoreDisabledForeignKey BIT = 0,
@IgnoreDisabledCheckConstraint BIT = 0,

-- This parameter will be filled regardless of @printsql or @executesql
@SQL VARCHAR(MAX) = NULL OUTPUT,

@PrintSQL BIT = 0, -- Display SQL in XML format (to overcome 8000 byte limitation)
@ExecuteSQL BIT = 0, -- Execute the SQL
@UseTransaction BIT = 0 -- Execute the SQL within a transaction

Post #1056809
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse