Bloggers

01-18-2012 / MAS 90 Shipping Software

01-10-2012 / MAS 90 Support Delightful As A Massage

12-09-2011 / MAS 90 Support That Fits Like a Glove

12-02-2011 / HubSpot Marketing Analytics Webcast

11-29-2011 / Automate 1099 Reporting and decrease your risk of IRS audit!

11-23-2011 / Do You Measure Marketing Return?

11-11-2011 / What are the most searched MAS 90 and MAS 200 enhancements? Take the quiz.

11-07-2011 / What happened at SEMA 2011?

10-26-2011 / ShipGear

10-17-2011 / Cloud Services

10-17-2011 / Intuit Summit 2011

10-14-2011 / Macola Distribution Pro and Macola Manufacturing Pro

08-31-2011 / Sage ERP MAS and SageCRM Combined in One Solution

08-19-2011 / Why Implement a CRM Strategy?

Tags Cloud

New MarketMedia Knowledgesync consulting Event Manager Macola back up Analytics QuickBooks Consulting Macola ES closing BirdDog software Lot Conrol Ron Ploof QuickBooks implementation Business System Implementations Small Business ERP approved suppliers list Ecoomerce QuickBooks Manufacturing Consultants Macola ES QuickBooks Manufacturing Microsoft Convergence Business Process Management Macola ES Support EDI Support MAS90 Consulting Business alerts workflow filters football AS 9100 QuickBooks advanced purchasing Exact Synergy Document Management Conditional Scheduled Report MAS 90 Full Value Support Plan ASL backup exec business process documentation Attivo Full Value Support Paperless document management Email notifications job shop QuickBooks ShipGear Efficiency Improvement Globe Enterprise ISO 9001 consultant Fishbowl Inventory ERP Software Implementation QuickBooks ProAdvisor IT infrastructure Quickbooks manufacuring Full Value Support Plan aerospace customers database backups

Follow Len's Tweets...

I disagree with Kay Jewelers. I would bet on any given Friday or Saturday night more kisses begin with tequila than Kay. 04-07-2011
Check this video out -- Accounting Software Lead Generation http://t.co/uSXS5ju via @youtube 02-22-2011
I guess I was rubbing in the great weather in Southern California yesterday...so I hereby extend an invitation to visit with us. 01-28-2011
More updates...

Home / Macola SQL tag

The Attivo Blog

SQL Server Made Easy - Finding Specific DATA in a database

06-12-2009 | By: Len Reo | (0) Post comment » | Read comments »

Looking for something? Let's say you're looking for a specific data value in a database, but you don't know which table or data elements contain the value. The SQL procedure below will scan all character string data elements in the database for the specified value. I. Create the procedure in the required database. SQL Script below: CREATE PROC SearchAllTables (@Search char(100) ) AS BEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue char(3630)) SET NOCOUNT ON DECLARE @Table char(256), @Column char(128), @XSearch char(110) SET @Table = '' SET @XSearch = QUOTENAME('%' + @Search + '%','''') WHILE @Table IS NOT NULL BEGIN SET @Column = '' SET @Table = (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @Table AND OBJECTPROPERTY(OBJECT_ID (QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME (TABLE_NAME)), 'IsMSShipped') = 0) WHILE (@Table IS NOT NULL) AND (@Column IS NOT NULL) BEGIN SET @Column = (SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA= PARSENAME(@Table, 2) AND TABLE_NAME = PARSENAME(@Table, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @Column) IF @Column IS NOT NULL BEGIN INSERT INTO #Results EXEC ('SELECT ''' + @Table+ '.' + @Column + ''', LEFT(' + @Column + ', 3630) FROM ' + @Table+ ' (NOLOCK) ' + ' WHERE ' + @Column + ' LIKE ' + @XSearch) END END END SELECT ColumnName, ColumnValue FROM #Results END II. To search all columns of all tables in database for the keyword "Computer" EXEC SearchAllTables 'value' This may be a long running process. The amount of time to execute is dependent on the size of the database you are scanning.