Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
helixpoint
Constraint Violating Yak Guru
291 Posts |
Posted - 2012-11-23 : 07:29:38
|
I have hundreads of tables that I need to search for one piece of data. Is there an easy was to search threw all columns in all tables for something like a code "p23lm"DaveHelixpoint Web Developmenthttp://www.helixpoint.com |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-23 : 07:44:27
|
[code]CREATE PROC SearchAllTables( @SearchStr nvarchar(100))ASBEGINDECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))SET NOCOUNT ONDECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)SET @TableName = ''SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')WHILE @TableName IS NOT NULLBEGIN 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') 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 ENDSELECT ColumnName, ColumnValue FROM @ResultsEND[/code]I did surfing for this Procedure. You can use it for your requirementand also see this link for more information:http://weblogs.sqlteam.com/brettk/archive/2005/02/08/4144.aspx--Chandu |
|
|
helixpoint
Constraint Violating Yak Guru
291 Posts |
Posted - 2012-11-23 : 09:11:18
|
Very Cool!!!! ThanxDaveHelixpoint Web Developmenthttp://www.helixpoint.com |
|
|
|
|
|
|
|