Sometimes i wonder about the Workflow triggering technique from the List and Document Library. I wonder because of one observation.
Let me share it with you, when you attach the workflow to a list or document library we have an option to configure it that when to trigger, is it on adding of the new item or is it on Updating the item. Adding a new item is a one time job, so for this scenario, it looks perfect for me.
But, when i consider trigger when updating item, it is something that i do not think this is done properly.
Because Once you open the ListItem for edit, do not edit anything and press OK. What happens, Still Workflow Triggers.
Now the question is, is it really cool to trigger the workflow even if you have not made any changes???????? I do not think so...
So bottom line is to check the proper condition in code and then dynamically from code, trigger the workflow.
Here is a way to trigger the workflow from code.
First you need to take the SPWorkflowManager Object.
SPWorkflowManager objWorkflowManager = null;
Then use SPWorkflowAssociationCollection object. every List and Document library has association with the workflow, to get this we have to use this object to collect all workflows which are associated with the List or DocumentLibrary.
Now consider a scenario, where you have multiple workflows associated with the same list or document library. So First we need to find the correct Workflow Association to trigger only that workflow.
So for that first Loop through all Associations,
foreach (SPWorkflowAssociation objWorkflowAssociation in objWorkflowAssociationCollection)
{ if (String.Compare(objWorkflowAssociation.BaseId.ToString("B"), {"Workflow_GUID"}, true) == 0)
{
//We found our workflow association that we want to trigger.
//Replace the workflow_GUID with the GUID of the workflow feature that you //have deployed.
objWorkflowManager.StartWorkflow(item, objWorkflowAssociation, objWorkflowAssociation.AssociationData, true); //The above line will start the workflow... break; } }
Sample Code:
using (SPSite site = new SPSite("http://localhost:777/")) { using (SPWeb web = site.OpenWeb()) { SPWorkflowManager manager = site.WorkflowManager; SPList InvoiceList = web.Lists["Invoices"]; SPWorkflowAssociation helloWorldAssociation = null; SPListItem item = InvoiceList.Items[0]; foreach (SPWorkflowAssociation association in InvoiceList.WorkflowAssociations) { if (association.BaseId == new Guid("64A0BC39-E987-4c39-9308-15F6511E8435")) { manager.StartWorkflow(item, association,"",true); } } } }
Posted by
milap shah
on
Wednesday, August 12, 2009
When I first started using SQL Server as a novice, I was initially confused as to the differences between clustered and non-clustered indexes. As a developer, and new DBA, I took it upon myself to learn everything I could about these index types, and when they should be used. This article is a result of my learning and experience, and explains the differences between clustered and non-clustered index data structures for the DBA or developer new to SQL Server. If you are new to SQL Server, I hope you find this article useful.
As you read this article, if you choose, you can cut and paste the code I have provided in order to more fully understand and appreciate the differences between clustered and non-clustered indexes.
Part I: Non-Clustered Index
Creating a Table
To better explain SQL Server non-clustered indexes; let’s start by creating a new table and populating it with some sample data using the following scripts. I assume you have a database you can use for this. If not, you will want to create one for these examples.
When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a “heap”. We can confirm the fact that this new table doesn’t have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. “Indid” refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not have an index, and is stored by SQL Server as a heap.
Now let’s add a few records in this table using this script:
Insert Into DummyTable1 Values (4, Replicate ('d',2000)) GO
Insert Into DummyTable1 Values (6, Replicate ('f',2000)) GO
Insert Into DummyTable1 Values (1, Replicate ('a',2000)) GO
Insert Into DummyTable1 Values (3, Replicate ('c',2000)) GO
Now, let’s view the contests of the table by executing the following command in Query Analyzer for our new table.
Select EmpID From DummyTable1 GO
Empid
4
6
1
3
As you would expect, the data we inserted earlier has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.
Now, let’s execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.
dbcc ind(dbid, tabid, -1) – This is an undocumented command.
This script will display many columns, but we are only interested in three of them, as shown below.
PagePID
IndexID
PageType
26408
0
10
26255
0
1
26409
0
1
Here’s what the information displayed means:
PagePID is the physical page numbers used to store the table. In this case, three pages are currently used to store the data.
IndexID is the type of index,
Where:
0 – Datapage
1 – Clustered Index
2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index),
PageType tells you what kind of data is stored in each database,
Where:
10 – IAM (Index Allocation MAP)
1 – Datapage
2 – Index page
Now, let us execute DBCC PAGE command. This is an undocumented command.
DBCC page(dbid, fileno, pageno, option)
Where:
dbid = database id.
Fileno = fileno of the page. Usually it will be 1, unless we use more than one file for a database.
Pageno = we can take the output of the dbcc ind page no.
Option = it can be 0, 1, 2, 3. I use 3 to get a display of the data. You can try yourself for the other options.
Run this script to execute the command:
DBCC TRACEON (3604) GO
DBCC page(@DBID, 1, 26408, 3) GO
The output will be page allocation details.
DBCC TRACEON (3604) GO
dbcc page(@DBID, 1, 26255, 3) GO
The data will be displayed in the order it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 both display the data page.
I have displayed the data page information for page 26255 only. This is how MS SQL stores the contents in data pages as such column name with its respective value.
This displays the exact data storage in SQL, without any index on table. Now, let’s go and create a unique non-clustered index on the EmpID column.
Creating a Non-Clustered Index
Now, we will create a unique non-clustered index on the empid column to see how it affects the data, and how the data is stored in SQL Server.
CREATE UNIQUE NONCLUSTERED INDEX DummyTable1_empid ON DummyTable1 (empid) GO
Now, execute the DBCC ind (dbid, tabid, -1)
DBCC TRACEON (3604) GO
Declare @DBID Int, @TableID Int Select @DBID = db_id(), @TableID = object_id('DummyTable1') DBCC ind(@DBID, @TableID, -1) GO
Here are the results:
PagePID
IndexID
PageType
26408
0
10
26255
0
1
26409
0
1
26411
2
10
26410
2
2
Now, we see two more rows than before, which now contains index page details. Page 26408 displays the page allocation details, and pages 26255 and 26409 display the data page details, as before.
In regard to the new pages, page 26411 displays the page allocation details of an index page and page 26410 displays the index page details.
MS SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.
Let us see what would be the output for page 26411, that is page type = 10
IAM: Single Page Allocations @0x308A608E
-----------------------------------------
Slot 0 = (1:26410)
Let us view page 26410 to see the index page details.
DBCC TRACEON (3604) GO
DBCC page(10, 1, 26410, 3) GO
SQL populates the index column data in order. The last column (?) is pointed to the row locator.
Here are the results, using two different methods:
Method I
FileID
PageID
EMPID
?
1
26410
1
0x8F66000001000200
1
26410
3
0x2967000001000000
1
26410
4
0x8F66000001000000
1
26410
6
0x8F66000001000100
The row location display in one of two ways:
If the table does not have a clustered index, the row locator will be combination of fileno, pageno and the no of rows in a page.
If the table does have clustered index, the row location will be clustered index key value.
Non-clustered indexes are particularly handy when we want to return a single row from a table.
For example, to search for employee ID (empid = 3) in a table that has a non-clustered index on the empid column, SQL Server looks through the index to find an entry that lists the exact page and row in the table where the matching empid can be found, and then goes directly to that page and row. This greatly speeds up accessing the record in question.
Select EmpID, EmpName From DummyTable1 WHERE EMPID = 3
Now, let’s insert some more rows in our table and view the data page storage of our non-clustered index.
Insert Into DummyTable1 Values (10, Replicate ('j',2000)) GO
Insert Into DummyTable1 Values (2, Replicate ('b',2000)) GO
Insert Into DummyTable1 Values (5, Replicate ('e',2000)) GO
Insert Into DummyTable1 Values (8, Replicate ('h',2000)) GO
Insert Into DummyTable1 Values (9, Replicate ('i',2000)) GO
Insert Into DummyTable1 Values (7, Replicate ('g',2000)) GO
Now, let’s view the data in our table.
Execute:
Select EmpID From DummyTable1
Here are the results:
EmpID
4
6
1
3
10
2
5
8
9
7
As you may notice above, the data is still in the order we entered it, and not in any particular order. This is because adding the non-clustered index didn’t change how the data was stored and ordered on the data pages.
Now, let’s view the results of the DBCC IND command. In order to find out what happened when the new data was added to the table.
DBCC TRACEON (3604) GO
Declare @DBID Int, @TableID Int Select @DBID = db_id(), @TableID = object_id('DummyTable1') DBCC ind(@DBID, @TableID, -1) GO
Here are the results:
PagePID
IndexID
PageType
26408
0
10
26255
0
1
26409
0
1
26412
0
1
26413
0
1
26411
2
10
26410
2
2
Let us execute the page 26410 again and get the index page details.
DBCC TRACEON (3604) GO
dbcc page(10, 1, 26410, 3) GO
SQL Server populates the index column data in order. The last column (?) is pointed to the row locator.
Here are the results:
Method I
FileID
PageID
EMPID
?
1
26410
1
0x8F66000001000200
1
26410
2
0x2C67000001000000
1
26410
3
0x2967000001000000
1
26410
4
0x8F66000001000000
1
26410
5
0x2C67000001000100
1
26410
6
0x8F66000001000100
1
26410
7
0x2D67000001000000
1
26410
8
0x2C67000001000200
1
26410
9
0x2967000001000200
1
26410
10
0x2967000001000100
As I explained earlier, there are two types of row locations. We have seen Method I. Now, let’s try Method II with the help of a clustered and non-clustered index in a table. DummyTable1 already has a non-clustered index. Let’s now add a new column to the DummyTabl1 table and add a clustered index on that column.
Alter Table DummyTable1 Add EmpIndex Int IDENTITY(1,1) GO
This will link the clustered index key value, instead of the row locator, and be will the combination of fileno, pageno and no of rows in a page.
This adds the Empindex column to DummyTable1. I have used an identity column so that we will not have null values on that column.
You can execute the DBCC ind and DBCC page to check if there any change after the new column is added to the table. If you don’t want to check this yourself, I can tell you that adding the new column did not affect the total number of pages currently allocated to the table by SQL Server.
Now, let’s add a unique clustered index on the empindex column and then view the differences in page 26410.
First, we execute the DBCC ind command. This displays a new set of pages for dummytable1.
DBCC TRACEON (3604) GO
Declare @DBID Int, @TableID Int Select @DBID = db_id(), @TableID = object_id('DummyTable1') DBCC ind(@DBID, @TableID, -1) GO
Here are the results:
PagePID
IndexID
PageType
26415
1
10
26414
0
1
26416
1
2
26417
0
1
26418
0
1
26420
2
10
26419
2
2
Pages 26415 and 26420 have page allocation details. Pages 26414, 26417 and 26418 have data page details.
Now, let’s view pages 26416 and 26419 and see the output.
DBCC TRACEON (3604) GO
DBCC page(10, 1, 26416, 3) GO
Here are the results:
FileID
PageID
ChildPageID
EMPID
1
26416
26414
0
1
26416
26417
5
1
26416
26418
9
This displays the output of the clustered index page, which has got a link to data page (ChildPageID). EMPID is an index column that contains the starting row of the page.
DBCC TRACEON (3604) GO
DBCC page(10, 1, 26419, 3) GO
Here are the results:
Method II
FileID
PageID
EMPID
EMPIndex
1
26419
1
1
1
26419
2
2
1
26419
3
3
1
26419
4
4
1
26419
5
5
1
26419
6
6
1
26419
7
7
1
26419
8
8
1
26419
9
9
1
26419
10
10
It is interesting to see the differences now. There is a difference between Method I and Method II. Method II is now linked to a clustered index key.
The main difference between Method I and Method IIis the link to a row in a data page.
Part II: Clustered Index
Creating a Table
To better explain how SQL Server creates clustered indexes; let’s start by creating a new table and populating it with some sample data using the following scripts. You can use the same sample database as before.
Create Table DummyTable2
( EmpId Int, EmpName Varchar(8000) )
As in the previous example, when you first create a new table, there is no index created by default, and a heap is created. As before, we can confirm the fact that this new table doesn’t have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. “Indid” refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not have an index, and is stored by SQL Server as a heap.
Now let’s add a few records in this table using this script:
Insert Into DummyTable2 Values (4, Replicate ('d',2000)) GO
Insert Into DummyTable2 Values (6, Replicate ('f',2000)) GO
Insert Into DummyTable2 Values (1, Replicate ('a',2000)) GO
Insert Into DummyTable2 Values (3, Replicate ('c',2000)) GO
Now, let’s view the contents of the table by executing the following command in Query Analyzer for our new table.
Select EmpID From DummyTable2 GO
Empid
4
6
1
3
As you would expect, the data we inserted has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.
Now, let’s execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.
DBCC ind(dbid, tabid, -1) – It is an undocumented command.
DBCC TRACEON (3604) GO
Declare @DBID Int, @TableID Int Select @DBID = db_id(), @TableID = object_id('DummyTable2') DBCC ind(@DBID, @TableID, -1) GO
This script will display many columns, but we are only interested in three of them, as shown below.
Here are the results:
PagePID
IndexID
PageType
26408
0
10
26255
0
1
26409
0
1
Here’s what the information displayed means:
PagePID is the physical page numbers used to store the table. In this case, three pages are currently used to store the data.
IndexID is the type of index,
Where:
0 – Datapage
1 – Clustered Index
2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index)
PageType tells you what kind of data is stored in each database
Where:
10 – IAM (Index Allocation MAP)
1 – Datapage
2 – Index page
Now, let us execute DBCC PAGE command.
DBCC page(dbid, fileno, pageno, option)
Where:
dbid = database id.
Fileno = fileno of the page. Usually it will be 1, unless we use more than one file for a database.
Pageno = we can take the output of the dbcc ind page no.
Option = it can be 0, 1, 2, 3. I use 3 to get a display of the data. You can try yourself for the other options.
Run this script to execute the command:
DBCC TRACEON (3604) GO
DBCC page(@DBID, 1, 26408, 3) GO
The output will be page allocation details.
DBCC TRACEON (3604) GO
DBCC page(@DBID, 1, 26255, 3) GO
The output will display the data however it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 will display the data page.
I have displayed the data page information for page 26255 only. This is how MS-SQL stores the contents in data pages as such column name with its respective value.
MS SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.
Now, let us view the page 26410 and 26412 and see the page details.
DBCC TRACEON (3604) GO
DBCC page(10, 1, 26412, 3) GO
Here are the results:
FileID
PageID
ChildPageID
EMPID
1
26412
26410
0
The output display many columns, but we are only interested in four of them as shown above.
This will display the output of the index page, which has got link to data page (ChildPageID). EMPID is an index column will contain the starting row of the page.
Now, let us view the page 26410 and see the page details.
Though I have added disorder records, SQL has displayed the data page in sequence because we have got a clustered index on empid. This is absolutely great! Adding a clustered index to the table has physically reordered the data pages, putting them in physical order based on the indexed column.
Now, let’s insert some more rows in our table and view the data and index page storage of our clustered index.
Insert Into DummyTable2 Values (10, Replicate ('j',2000))
GO
Insert Into DummyTable2 Values (2, Replicate ('b',2000))
GO
Insert Into DummyTable2 Values (5, Replicate ('e',2000))
GO
Insert Into DummyTable2 Values (8, Replicate ('h',2000))
GO
Insert Into DummyTable2 Values (9, Replicate ('i',2000))
GO
Insert Into DummyTable2 Values (7, Replicate ('g',2000))
GO
Now, we see few more rows than before. Page 26411 displays the page allocation details, and pages 26408, 26409, 26410 and 26255 display the data page details, as before.
In regard to the new pages, page 26411 displays the page allocation details of an index page and 26412 displays the index page details.
MS-SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.
Let us see what would be the output for page 26411, that is page type = 10.
DBCC TRACEON (3604)
GO
dbcc page(10, 1, 26411, 3)
GO
Here are the results:
IAM: Single Page Allocations @0x30A5C08E
-----------------------------------------
Slot 0 = (1:26410)
Slot 1 = (1:26412)
Slot 2 = (1:26255)
Slot 3 = (1:26408)
Slot 4 = (1:26409)
Let us view page 26412 to see the index page details.
DBCC TRACEON (3604)
GO
DBCC page(10, 1, 26412, 3)
GO
Here are the results:
FileID
PageID
ChildPageID
EMPID
1
26412
26410
0
1
26412
26408
4
1
26412
26255
6
1
26412
26409
9
This helps us to get an idea to decide the need of clustered index. It is really useful to have a clustered index when retrieve many rows of data, ranges of data, and when BETWEEN is used in the WHERE clause. Because, the leaf level of the clustered index is the data. It should be used to save many I/Os. So, it is better to use clustered indexes to solve queries asking for ranges of data, not one row.
For example, to search for an employee ID (empid between 3 and 9) in a table that has a clustered index on the empid column.
Select EmpID, EmpName From DummyTable1 WHEREEMPID Between 3 And 9
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.SharePoint.Administration; using Microsoft.SharePoint; using System.IO;
namespace SampleTimerJob { public class TaskCreatorJob :SPJobDefinition { public TaskCreatorJob() : base() { } public TaskCreatorJob(string jobName, SPService service, SPServer server, SPJobLockType targetType) : base(jobName, service, server, targetType) {
}
public TaskCreatorJob(string jobName, SPWebApplication webApplication)
PRINT 'Checking for the existence of this procedure' IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists BEGIN PRINT 'Procedure already exists. So, dropping it' DROP PROC sp_generate_inserts END GO
--Turn system object marking on EXEC master.dbo.sp_MS_upd_sysobj_category 1 GO
CREATE PROC sp_generate_inserts ( @table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data @target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted @include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement @from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE) @include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement @debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination @owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table @ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns @ommit_identity bit = 0, -- Use this parameter to ommit the identity columns @top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows @cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement @cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement @disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements @ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement
Purpose: To generate INSERT statements from existing data. These INSERTS can be executed to regenerate the data at some other location. This procedure is also useful to create a database setup, where in you can script your data along with your table definitions.
Written by: Narayana Vyas Kondreddi http://vyaskn.tripod.com
Acknowledgements: Divya Kalra -- For beta testing Mark Charsley -- For reporting a problem with scripting uniqueidentifier columns with NULL values Artur Zeygman -- For helping me simplify a bit of code for handling non-dbo owned tables Joris Laperre -- For reporting a regression bug in handling text/ntext columns
Tested on: SQL Server 7.0 and SQL Server 2000
Date created: January 17th 2001 21:52 GMT
Date modified: May 1st 2002 19:50 GMT
Email: vyaskn@hotmail.com
NOTE: This procedure may not work with tables with too many columns. Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts like nchar and nvarchar
Example 1: To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'
Example 2: To ommit the column list in the INSERT statement: (Column list is included by default) IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below, to avoid erroneous results
Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4: To generate INSERT statements for 'titles' table for only those titles which contain the word 'Computer' in them: NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter
EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"
Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement: (By default TIMESTAMP column's data is not scripted)
Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name To use this option, you must have SELECT permissions on that table
Example 8: To generate INSERT statements for the rest of the columns excluding images When using this otion, DO NOT set @include_column_list parameter to 0.
Example 14: To exclude computed columns from the INSERT statement: EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1 ***********************************************************************************************************/
SET NOCOUNT ON
--Making sure user only uses either @cols_to_include or @cols_to_exclude IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL)) BEGIN RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1) RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified END
--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0)) BEGIN RAISERROR('Invalid use of @cols_to_include property',16,1) PRINT 'Specify column names surrounded by single quotes and separated by commas' PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"' RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property END
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0)) BEGIN RAISERROR('Invalid use of @cols_to_exclude property',16,1) PRINT 'Specify column names surrounded by single quotes and separated by commas' PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"' RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property END
--Checking to see if the database name is specified along wih the table name --Your database context should be local to the table for which you want to generate INSERT statements --specifying the database name is not allowed IF (PARSENAME(@table_name,3)) IS NOT NULL BEGIN RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1) RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed END
--Checking for the existence of 'user table' or 'view' --This procedure is not written to work on system tables --To script the data in system tables, just create a view on the system tables and script the view instead
IF @owner IS NULL BEGIN IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL)) BEGIN RAISERROR('User table or view not found.',16,1) PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.' PRINT 'Make sure you have SELECT permission on that table or view.' RETURN -1 --Failure. Reason: There is no user table or view with this name END END ELSE BEGIN IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner) BEGIN RAISERROR('User table or view not found.',16,1) PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.' PRINT 'Make sure you have SELECT permission on that table or view.' RETURN -1 --Failure. Reason: There is no user table or view with this name END END
--Variable declarations DECLARE @Column_ID int, @Column_List varchar(8000), @Column_Name varchar(128), @Start_Insert varchar(786), @Data_Type varchar(128), @Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements @IDN varchar(128) --Will contain the IDENTITY column's name in the table
--Variable Initialization SET @IDN = '' SET @Column_ID = 0 SET @Column_Name = '' SET @Column_List = '' SET @Actual_Values = ''
IF @owner IS NULL BEGIN SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' END ELSE BEGIN SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' END
--To get the first column's ID
SELECT @Column_ID = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME = @table_name AND (@owner IS NULL OR TABLE_SCHEMA = @owner)
--Loop through all the columns of the table, to get the column names and their data types WHILE @Column_ID IS NOT NULL BEGIN SELECT @Column_Name = QUOTENAME(COLUMN_NAME), @Data_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE ORDINAL_POSITION = @Column_ID AND TABLE_NAME = @table_name AND (@owner IS NULL OR TABLE_SCHEMA = @owner)
IF @cols_to_include IS NOT NULL --Selecting only user specified columns BEGIN IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0 BEGIN GOTO SKIP_LOOP END END
IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns BEGIN IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0 BEGIN GOTO SKIP_LOOP END END
--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1 BEGIN IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column SET @IDN = @Column_Name ELSE GOTO SKIP_LOOP END
--Making sure whether to output computed columns or not IF @ommit_computed_cols = 1 BEGIN IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1 BEGIN GOTO SKIP_LOOP END END
--Tables with columns of IMAGE data type are not supported for obvious reasons IF(@Data_Type in ('image')) BEGIN IF (@ommit_images = 0) BEGIN RAISERROR('Tables with image columns are not supported.',16,1) PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.' PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.' RETURN -1 --Failure. Reason: There is a column with image data type END ELSE BEGIN GOTO SKIP_LOOP END END
--Determining the data type of the column and depending on the data type, the VALUES part of --the INSERT statement is generated. Care is taken to handle columns with NULL values. Also --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns SET @Actual_Values = @Actual_Values + CASE WHEN @Data_Type IN ('char','varchar','nchar','nvarchar') THEN 'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')' WHEN @Data_Type IN ('datetime','smalldatetime') THEN 'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')' WHEN @Data_Type IN ('uniqueidentifier') THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')' WHEN @Data_Type IN ('text','ntext') THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')' WHEN @Data_Type IN ('binary','varbinary') THEN 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')' WHEN @Data_Type IN ('timestamp','rowversion') THEN CASE WHEN @include_timestamp = 0 THEN '''DEFAULT''' ELSE 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')' END WHEN @Data_Type IN ('float','real','money','smallmoney') THEN 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')' ELSE 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')' END + '+' + ''',''' + ' + '
--Generating the column list for the INSERT statement SET @Column_List = @Column_List + @Column_Name + ','
SKIP_LOOP: --The label used in GOTO
SELECT @Column_ID = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME = @table_name AND ORDINAL_POSITION > @Column_ID AND (@owner IS NULL OR TABLE_SCHEMA = @owner)
--Loop ends here! END
--To get rid of the extra characters that got concatenated during the last run through the loop SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1) SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
IF LTRIM(@Column_List) = '' BEGIN RAISERROR('No columns to select. There should at least be one column to generate the output',16,1) RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter END
--Forming the final string that will be executed, to output the INSERT statements IF (@include_column_list <> 0) BEGIN SET @Actual_Values = 'SELECT ' + CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + '''' + RTRIM(@Start_Insert) + ' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' + ' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' + COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)') END ELSE IF (@include_column_list = 0) BEGIN SET @Actual_Values = 'SELECT ' + CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + '''' + RTRIM(@Start_Insert) + ' '' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' + COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)') END
--Determining whether to ouput any debug information IF @debug_mode =1 BEGIN PRINT '/*****START OF DEBUG INFORMATION*****' PRINT 'Beginning of the INSERT statement:' PRINT @Start_Insert PRINT '' PRINT 'The column list:' PRINT @Column_List PRINT '' PRINT 'The SELECT statement executed to generate the INSERTs' PRINT @Actual_Values PRINT '' PRINT '*****END OF DEBUG INFORMATION*****/' PRINT '' END
PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas' PRINT '--Build number: 22' PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com' PRINT '--http://vyaskn.tripod.com' PRINT '' PRINT 'SET NOCOUNT ON' PRINT ''
--Determining whether to print IDENTITY_INSERT or not IF (@IDN <> '') BEGIN PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON' PRINT 'GO' PRINT '' END
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL) BEGIN IF @owner IS NULL BEGIN SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily' END ELSE BEGIN SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily' END
--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes! EXEC (@Actual_Values)
PRINT 'PRINT ''Done''' PRINT ''
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL) BEGIN IF @owner IS NULL BEGIN SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints' END ELSE BEGIN SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints' END
PRINT 'GO' END
PRINT '' IF (@IDN <> '') BEGIN PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF' PRINT 'GO' END
PRINT 'SET NOCOUNT OFF'
SET NOCOUNT OFF RETURN 0 --Success. We are done! END
GO
PRINT 'Created the procedure' GO
--Turn system object marking off EXEC master.dbo.sp_MS_upd_sysobj_category 2 GO
PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users' GRANT EXEC ON sp_generate_inserts TO public
