Quickly delete all products and orders from uCommerce
Saturday, May 11, 2013 11:09:59 AM (GMT Daylight Time, UTC+01:00)
Sometimes you need to blitz your uCommerce database e.g. just before launch or to remove the testing products etc.
This is a quick database clear script which will clear delete all orders and products in uCommerce.
Be careful - this will remove everything without any form of checks (this is the V3 script let me know if you need it for other versions).
BEGIN TRAN
DELETE FROM uCommerce_ProductReviewComment
DELETE FROM uCommerce_ProductReview
DELETE FROM uCommerce_OrderLineDiscountRelation
DELETE FROM uCommerce_ShipmentDiscountRelation
DELETE FROM uCommerce_Discount
UPDATE uCommerce_OrderLine SET ShipmentId = NULL
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL
DELETE FROM uCommerce_Shipment
DELETE FROM uCommerce_OrderAddress
DELETE FROM uCommerce_OrderProperty
DELETE FROM uCommerce_OrderLine
DELETE FROM uCommerce_PaymentProperty
DELETE FROM uCommerce_Payment
DELETE FROM uCommerce_OrderStatusAudit
DELETE FROM uCommerce_PurchaseOrder
DELETE FROM uCommerce_Address
DELETE FROM uCommerce_Customer
DELETE uCommerce_ProductRelation
DELETE uCommerce_ProductProperty
DELETE uCommerce_ProductDescriptionProperty
DELETE uCommerce_ProductDescription
DELETE uCommerce_CategoryProductRelation
DELETE uCommerce_PriceGroupPrice
DELETE FROM uCommerce_Product
-- Just double check things have gone
SELECT * FROM uCommerce_PurchaseOrder o
SELECT * FROM uCommerce_Product p
-- For safety's sake, run it in a transaction just in case you change your mind
ROLLBACK TRAN
-- When happy it works, uncomment this line and comment out the ROLLBACK
-- COMMIT TRAN
How to hide a tree node in uCommerce or Umbraco
Wednesday, August 31, 2011 10:37:04 AM (GMT Daylight Time, UTC+01:00)
Have you ever needed to hide a node in the Umbraco or uCommerce trees? It's actually very easy, we needed to hide the Orders, Marketing and Analytics nodes of a new uCommerce install we were working on. All you need to do is set the "treeInitialize" value in the umbracoAppTree to false. This will then hide the entire tree.
The Update SQL
BEGIN TRAN
UPDATE dbo.umbracoAppTree SET treeInitialize = '0' WHERE appAlias = 'uCommerce' AND treeTitle = 'Analytics'
ROLLBACK TRAN
To use the SQL you will need to know the appAlias (this is the bit after the # in the Umbraco admin url once you've clicked the section icon e.g. in "http://www.domain.com/umbraco/umbraco.aspx#uCommerce" the appAlias is uCommerce). If you know the treeAlias it might be better to use that but it's probably easier to use the tree's title (in our case this would be Orders, Marketing and Analytics).
Not sure what tree you should be hiding? Just open the umbracoAppTree table and you'll have the trees from all sections there.
uCommerce tree before
uCommerce tree after
Couldn't be any easier could it!
Remove uCommerce Product Definition Field in SQL
Thursday, March 03, 2011 8:11:10 AM (GMT Standard Time, UTC+00:00)
Sometimes you need to remove a product definition field from uCommerce e.g. one created in a test environment. Although you can just right click and click "delete" within the administration area, this sometimes doesn't work e.g. when it's a pre-release so this is a simple script which allows you to remove a product definition field from the database.
USE [YourDatabaseName]
GO
BEGIN TRANSACTION
-- Get a list of the current product definitions
SELECT * FROM dbo.uCommerce_ProductDefinition
DECLARE @ProductDefinitionId int, @ProductDefinitionFieldId int
SET @ProductDefinitionId = 23
-- Check that this is the right product definition
SELECT * FROM dbo.uCommerce_ProductDefinition WHERE ProductDefinitionId = @ProductDefinitionId
-- Get a break down of the various fields for the product definition
SELECT * FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId
-- Set the field id
SET @ProductDefinitionFieldId = 40
-- Check the right field and descriptions will be removed
SELECT * FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductDefinitionFieldDescription d ON f.ProductDefinitionFieldId = d.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId
SELECT * FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductProperty p ON f.ProductDefinitionFieldId = p.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId
SELECT * FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId AND ProductDefinitionFieldId = @ProductDefinitionFieldId
-- Remove any product property definitions
DELETE p FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductProperty p ON f.ProductDefinitionFieldId = p.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId
-- Remove any associated descriptions
DELETE d FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductDefinitionFieldDescription d ON f.ProductDefinitionFieldId = d.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId
-- Remove the field itself
DELETE FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId AND ProductDefinitionFieldId = @ProductDefinitionFieldId
ROLLBACK TRANSACTION
-- When you're happy, uncomment this line
--COMMIT TRANSACTION
SQL Server: Warning: Fatal error 823 occurred at date / time Note the error and time, and contact your system administrator.
Tuesday, December 14, 2010 12:46:42 PM (GMT Standard Time, UTC+00:00)
![error[2]](http://blogs.thesitedoctor.co.uk/tim/images/616c06524d21_ABF1/error2.png "error[2]")
We were contacted the other day by a client with issues selecting data from one of their tables after a recent server crash (not running on our servers or a site that we were involved in developing). The issue was easy enough to recreate as you just needed to select records after the server crash and you'd get the error:
Warning: Fatal error 823 occurred at date / time Note the error and time, and contact your system administrator.
A quick Google suggests a physical disk drive error and having a quick look at the issues it wasn't pretty. Running:
DBCC CHECKDB('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGSResulted in:
Msg 8909, Level 16, State 1, Line 5
Table error: Object ID 0, index ID 12341, page ID (1:5880). The PageId in the page header = (9728:16777220).
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
....
DBCC results for 'TableName'.
Msg 8928, Level 16, State 1, Line 5
Object ID 871674153, index ID 0: Page (1:5880) could not be processed. See other errors for details.
....
There are 20993 rows in 584 pages for object 'TableName'.
CHECKTABLE found 0 allocation errors and 8 consistency errors in table 'TableName' (object ID 871674153).
Msg 8909, Level 16, State 1, Line 5
Table error: Object ID 1109413712, index ID 24940, page ID (1:5883). The PageId in the page header = (25198:1632843825).
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 1109413712)' (object ID 1109413712).
Most of the solutions found on Google resulted in some form of system restore but that's no good in this instance as the backups only existed for after the problem was identified (great eh!) so were useless.
Although it's not an ideal solution, you can use DBCC CHECKTABLE which in our case fixed the issue:
--Put the database into single user mode
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH NO_WAIT
--Check the erors and fix any issues found (that you can)
DBCC CHECKTABLE ('Orders', REPAIR_REBUILD)
--Put the database back into multiuser mode
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT
I'd be interested to know other solutions people may have to this issue.
Note to readers: Check that your hosting provider performs regular backups and checks the health of your server regularly to avoid this happening to you.
Download any Umbraco document type’s values as crosstab/Excel table
Tuesday, November 09, 2010 9:16:24 PM (GMT Standard Time, UTC+00:00)
In a follow up to my post yesterday -How to download Umbraco content properties into a crosstab table this is the follow up SQL Script that makes it even easier to download any Umbraco document type into Excel.
This SQL Script is fairly simple, basically what it does is it gets the properties associated with the specified document type and then pivots the values so you end up with a table of data that looks like this:
| Id | Property 1 | Property 2 | Property 3 | Property n |
| 123 | String | Int | Date | xxx |
How to use the script
All you need to do is set the parameter "@ContentTypeId" to the document type you want (as in my previous post you can get this by checking out the link on the document type).
Once you set the id, just run the script and voila there's the data.
If you run the code and get "Command(s) completed successfully" then you've not set the id right so double check and try again.
The Script
DECLARE @cols NVARCHAR(max), @ContentTypeId int
SET @ContentTypeId = 1074
SELECT @cols = STUFF((
SELECT DISTINCT TOP 100 PERCENT
'],['
+ CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255)
FROM
dbo.cmsPropertyType
WHERE
contentTypeId = @ContentTypeId
ORDER BY
'],['
+ CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255)
FOR XML PATH('')
), 1, 2, '') + ']'
--SELECT @cols
DECLARE @query NVARCHAR(max)
SET @query = N'SELECT Id, ' + @cols + '
FROM
(
SELECT
CONVERT(varchar, t.Name + '' ('' + CONVERT(varchar, t.id) + '')'', 255) As [PropId],
contentNodeId As [Id],
ISNULL(dataNvarchar, ISNULL(CONVERT(varchar, dataDate), ISNULL(CONVERT(varchar, dataInt), dataNtext))) As [Value]
FROM
dbo.cmsPropertyType t LEFT JOIN dbo.cmsPropertyData d ON t.id = d.propertytypeid
WHERE
contentTypeId = ' + CONVERT(varchar, @ContentTypeId) + '
) p
PIVOT
(
MAX(Value)
FOR PropId IN ( '+ @cols +' )
) AS pvt
ORDER BY Id ASC'
--PRINT(@query)
EXECUTE(@query)
Download Umbraco content properties into a crosstab table
Monday, November 08, 2010 4:37:19 PM (GMT Standard Time, UTC+00:00)
Have you ever needed to get a download of your Umbraco content nodes in a cross-tab query e.g. download contact form data from Doc2Form? Using Umbraco's Contour product makes this a breeze but what about older systems? Thankfully, it's not actually that difficult.
We have a contact us form on one of our sites which uses an old version of Doc2Form which emails the customer details of the enquiry. One benefit is it also saves it to the recycle bin as a document with the name: "RE: SYSTEM DATA: umbraco master root". With that in mind, we can use SQL Server's PIVOT functionality to pull the data out in a nicely formatted manner.
Firstly you'll need to know the id's of the document type's properties, there are numerous ways to do this:
1. Just look at the cmsPropertyData table for a couple of content nodes (I could spot the ones I was after fairly easily)
2. Query the cmsPropertyType table:
Find the contentTypeId of the document type -you can do this by hovering your mouse over the document type in the tree and checking out the status bar (you can see the contentTypeId in brackets in the bottom left -mine here is 1074):
Once you have the contentTypeId of the document type, you can then get the ids of all the properties you're after by replacing "xxx" with your property id in the following script:
SELECT * FROM dbo.cmsPropertyType WHERE contentTypeId = xxx
3. Get it from the source of the document type editor
An alternative way is to examine the HTML of the Document Type editor. If you view the source on the "Generic Properties" tab and scroll to the section you're interested in (there'll be a h2 with the same name) you will find a ul that has the class of "genericPropertyList".
Each li of that ul will have the relevant id as part of it's id e.g. for a section called "Enquiry Form" the id will be: "EnquiryFormContents_49" where "49" is the id we're interested in. You can see mine (49, 50, 51 and 52 below):
Once you have these ids to hand (mine were 49, 50, 51 and 52) you just need to update the code below and run against your Umbraco database:
SELECT
contentNodeId As [Id],
[49] As [Name],
[50] As [Telephone],
[51] As [Email Address],
[52] As [Notes]
FROM
(
SELECT contentNodeId, propertytypeid, ISNULL(dataNvarchar, dataNtext) As [Value]
FROM dbo.cmsPropertyData
) As src
PIVOT (
MAX(Value)
FOR propertytypeid in ([49], [50], [51], [52])
) aS pvt
WHERE [50] IS NOT NULL OR [51] IS NOT NULL
ORDER BY contentNodeId
That will then produce some lovely formatted data for you, my example above produced:
| Id |
Name |
Telephone |
Email Address |
Notes |
| 1154 |
Example |
01234567890 |
test@test.com |
Please contact me as soon as possible about your great site, thanks. |
It's also possible to automate this entire script so you don't need to find out the property ids, I'll post that separately.
Delete all UCommerce baskets older than x days
Monday, October 04, 2010 10:14:31 AM (GMT Daylight Time, UTC+01:00)
After my last UCommerce post on how to delete test orders and baskets from UCommerce, Søren suggested I extended the delete all baskets code to take into account when it was created. As my last code was relating to deleting test orders/baskets (and so would want to get rid of them all), I decided to post this one separately.
Delete all baskets older than x days
To use this, all you need to do is change the @addedBefore parameter to whatever date/time you want (or just adjust the –7 which represents seven days in the past.
--Delete all carts purchaseorders and associated data within x days
DECLARE @addedBefore smalldatetime
--By default the script deletes everything older than 7 days
SET @addedBefore = DATEADD(dd, -7, GETDATE())
BEGIN TRAN
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore
--Uncomment this
--COMMIT TRAN
--And comment out this
ROLLBACK TRAN
Deleting test orders and baskets from uCommerce
Friday, October 01, 2010 12:53:43 PM (GMT Daylight Time, UTC+01:00)
Although Søren has posted a helpful post on how to delete entire purchase orders from the database here, we needed something a little less “all or nothing” so put the below together.
Delete a specific order id
--Delete purchaseorders and associated data based on order id
DECLARE @OrderNumber nvarchar(50)
SET @OrderNumber = 'TEST-40'
BEGIN TRAN
UPDATE a SET ShipmentId = NULL FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE OrderNumber IS NULL
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber = @OrderNumber
--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
--Uncomment this
--COMMIT TRAN
--And comment out this
ROLLBACK TRAN
Delete all baskets
--Delete all carts purchaseorders and associated data
BEGIN TRAN
UPDATE a SET ShipmentId = NULL FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE OrderNumber IS NULL
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL
--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL
--Uncomment this
--COMMIT TRAN
--And comment out this
ROLLBACK TRAN
Update: At the request of Søren, I’ve altered the delete all baskets post so it allows you to delete all baskets older than a given date, see: Delete all UCommerce baskets older than x days
Search every table and field in a SQL Server Database Updated
Friday, February 19, 2010 12:07:28 PM (GMT Standard Time, UTC+00:00)
As some of my blog posts are a little out of date, I thought I would spend some time updating the most popular ones. As I use this script on a regular basis and there was an error with the original posting, I thought I'd update it with a "corrected" version to get things started.
If you want to see the original script, you can refer to How to search every table and field in a SQL Server Database. This one's just fixed :)
As a stand alone script
DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results
As a re-usable stored procedure
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
DROP TABLE #Results
END