January 12, 2012 at 4:17 am
Ivan,
please check out BOL on SUBSTRING and you'll find out that this command will only work with STRINGS, but not with numeric values.
Just remove the SUBSTRING section from the last EXEC section and you're good to go.
- Michael
January 12, 2012 at 4:18 am
Steve Jones - SSC Editor (1/9/2012)
Use a script like this: http://www.sqlservercentral.com/scripts/Miscellaneous/65769/Change the where from a "column like @value" to "datalength(column) = 9"
If you want 9 digit values.
You think you're asking for something simple, but either we have a language barrier or you don't understand what you're asking, because what you're posting doesn't make sense in English. It doesn't provide a description that can be translated to code.
I went through Your script .If u can past the script and guide me where to make the change as because the script through lot of error when i parse it
January 12, 2012 at 4:34 am
Ivan Mohapatra (1/12/2012)
Steve Jones - SSC Editor (1/9/2012)
Use a script like this: http://www.sqlservercentral.com/scripts/Miscellaneous/65769/Change the where from a "column like @value" to "datalength(column) = 9"
If you want 9 digit values.
You think you're asking for something simple, but either we have a language barrier or you don't understand what you're asking, because what you're posting doesn't make sense in English. It doesn't provide a description that can be translated to code.
I went through Your script .If u can past the script and guide me where to make the change as because the script through lot of error when i parse it
Ivan,
as I've already mentioned--just remove the SUBSTRING portion from the last EXEC() statement and you won't receive the error any more. No need for more scripts--you've already received all possible variants.
Here's the part that needs to be changed (as it should look to work):
'SELECT ''' + @TableName + '.' + @ColumnName + ''',' + @ColumnName
+ ' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
Cheers.
January 12, 2012 at 5:09 am
michael.kaufmann (1/12/2012)
Ivan Mohapatra (1/12/2012)
Steve Jones - SSC Editor (1/9/2012)
Use a script like this: http://www.sqlservercentral.com/scripts/Miscellaneous/65769/Change the where from a "column like @value" to "datalength(column) = 9"
If you want 9 digit values.
You think you're asking for something simple, but either we have a language barrier or you don't understand what you're asking, because what you're posting doesn't make sense in English. It doesn't provide a description that can be translated to code.
I went through Your script .If u can past the script and guide me where to make the change as because the script through lot of error when i parse it
Ivan,
as I've already mentioned--just remove the SUBSTRING portion from the last EXEC() statement and you won't receive the error any more. No need for more scripts--you've already received all possible variants.
Here's the part that needs to be changed (as it should look to work):
'SELECT ''' + @TableName + '.' + @ColumnName + ''',' + @ColumnName
+ ' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
Cheers.
GO
/****** Object: StoredProcedure [dbo].[SearchAllTablesAllColumns] Script Date: 01/12/2012 17:29:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SearchAllTablesAllColumns]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT
SELECT @TableName = '', @ColumnName = ''
SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')
WHILE (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName = (
SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)
+ '|' + QUOTENAME(C.Column_name))
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND C.DATA_TYPE IN ('varchar','int')
AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName
)
SET @Parse = PATINDEX ('%|%', @ColumnName)
SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)
SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''',' + @ColumnName
+ ' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
)
END
END
SELECT ColumnName, ColumnValue FROM #Results
ORDER BY ColumnName
END
This is how it look after changing but can i confined the search to 9 digit like the
SearchAllTablesAllColumns '5________' as when i tried it IT shows alphabet also as 'ABC' i only need 9 digits value.like 999999999,000000999 etc.
kindly guide me
January 12, 2012 at 5:36 am
Ivan Mohapatra (1/12/2012)
This is how it look after changing but can i confined the search to 9 digit like theSearchAllTablesAllColumns '5________' as when i tried it IT shows alphabet also as 'ABC' i only need 9 digits value.like 999999999,000000999 etc.
kindly guide me
Check out BOL on function ISNUMERIC().
Modify your WHERE clause in the statement you just amended--sorry but I cannot do your work for you.
Cheers,
Michael
January 12, 2012 at 5:48 am
THANKS
(michael.kaufmann & jnuqui) After modifying the final script which i have tested in a small DB it work fine and still yet to be tested in big DB. and Isnumeric function doesn't work in Varchar datatype.
HOPE for the Best and positive hope for testing it in Live DB.
/****** Object: StoredProcedure [dbo].[SearchAllTablesAllColumns] Script Date: 01/12/2012 18:00:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SearchAllTablesAllColumns]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT
SELECT @TableName = '', @ColumnName = ''
SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')
WHILE (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName = (
SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)
+ '|' + QUOTENAME(C.Column_name))
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND C.DATA_TYPE IN ('varchar','int')
AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName
)
SET @Parse = PATINDEX ('%|%', @ColumnName)
SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)
SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''',' + @ColumnName
+ ' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
)
END
END
SELECT distinct(ColumnValue), ColumnName FROM #Results
ORDER BY ColumnName
END
October 25, 2012 at 1:41 pm
This script (meaning the first one in this thread to search all tables) works perfectly for us. I cannot thank enough the person who wrote it.
Eric
November 29, 2012 at 2:01 am
Hi... from search i found an sql statment mostly same as yours...
the problem is which when searching uniqueidentifier.... It just dont work at my side...
Anyway, SQL Statement is stated below..
USE ServiceManager;
DECLARE @GUID VARCHAR(MAX);
DECLARE @columnValue VARCHAR(MAX)
DECLARE @searchTableName VARCHAR(MAX);
DECLARE @managementPackId VARCHAR(MAX);
SET @columnValue = '%67C8B21E-47D5-3835-01B1-28BD72258E62%';
SET @searchTableName = '%';
DECLARE @managementPackTable TABLE ( TABLE_NAME VARCHAR(MAX), COLUMN_NAME VARCHAR(MAX) );
DECLARE @generatedSQLTable TABLE ( SQLStatement VARCHAR(MAX) );
DECLARE @tableName VARCHAR(MAX), @columnName VARCHAR(MAX);
DECLARE @ctr INT;
DECLARE managementPackTable_Cursor CURSOR FOR
SELECT INFORMATION_SCHEMA.COLUMNS.TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
INNER JOIN INFORMATION_SCHEMA.TABLES ON
INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND
INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = 'dbo'
WHERE
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME LIKE @searchTableName AND
INFORMATION_SCHEMA.TABLES.TABLE_TYPE <> 'view' AND
DATA_TYPE IN ('nvarchar', 'varchar')
SET @ctr = null;
OPEN managementPackTable_Cursor;
FETCH managementPackTable_Cursor INTO @tableName, @columnName;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sqlString NVARCHAR(MAX);
SET @sqlString = N'SELECT @counter = COUNT(' + @tableName + '.' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' LIKE ''' + @columnValue + '''';
INSERT INTO @generatedSQLTable (SQLStatement) VALUES (@sqlString);
execute sp_executesql @sqlstring, N'@counter INT OUTPUT', @counter=@ctr OUTPUT;
IF @ctr > 0
BEGIN
INSERT INTO @managementPackTable ( TABLE_NAME, COLUMN_NAME ) VALUES ( @tableName, @columnName );
END
FETCH managementPackTable_Cursor INTO @tableName, @columnName;
END
DEALLOCATE managementPackTable_Cursor;
SELECT * FROM @managementPackTable;
SELECT * FROM @generatedSQLTable;
Thanks in advance man...
November 29, 2012 at 2:10 am
my mistake.. sorry.... was just careless bout this...
from
DATA_TYPE IN ('nvarchar', 'varchar')
to
DATA_TYPE IN ('nvarchar', 'varchar','uniqueidentifier')
:rolleyes:
April 8, 2013 at 3:46 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTERPROCEDURE[dbo].[usp_SEARCH_DATABASE_FOR_STRING]
(
@SearchStringVarchar(1000),
@SearchTypeVarchar(6)= 'EQUALS',
@StringTypeVarchar(8)= 'Text'
)
AS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
--PURPOSE:THIS PROCEDURE WILL SEARCH THE ENTIRE DATABASE AND IDENTITY THE TABLE AND COLUMN THAT CONTAINS A SPECIFIC SEARCH STRING AND THE COUNT OF THE SEARCH STRING IN EACH TABLE\COLUMN
--COMBINATION. THE RESULTS ARE DISPLAY IN A QUERY RESULT SET.
--
--It can be extended to search for Date and Money and float data types, just copy the code for Integer Number and change the data type to Datetime, Datetime2 or money, etc., and
--add another values for the @StringType parameter such as 'DATE' or 'MONEY' or 'FLOAT'
--
--AUTHOR:STEVE KIRCHNER
--
--CREATED:04/06/2013
--
--SAMPLE EXEC:
--
/*
EXECusp_SEARCH_DATABASE_FOR_STRING
@SearchString='ammo',
@SearchType='EQUALS',
@StringType='TEXT'
EXECusp_SEARCH_DATABASE_FOR_STRING
@SearchString='WEAPON',
@SearchType='LIKE',
@StringType='TEXT'
EXECusp_SEARCH_DATABASE_FOR_STRING
@SearchString='1',
@SearchType='EQUALS',
@StringType='NUMBER'
EXECusp_SEARCH_DATABASE_FOR_STRING
@SearchString='1',
@SearchType='LIKE',
@StringType='NUMBER'
*/
--
--
--
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE@IINT
DECLARE@SQLVarchar(MAX)
DECLARE@SQLqueriesTable
(
IDINTIDENTITY(1,1),
SQLstatement nvarchar(max)
)
CREATETable##Results
(
IDINTIDENTITY(1,1),
TableNamenvarchar(750),
ColumnNamenvarchar(750),
SearchValue nvarchar(max),
Found_Count int
)
IF@StringType= 'TEXT'
IF@SearchType = 'EQUALS'
INSERT@SQLqueries
SELECT 'INSERT ##Results (Tablename, ColumnName, SearchValue, Found_Count) SELECT ''' + TABLE_NAME +''' as Tname, ''' + column_name + ''' as Cname, [' + column_name + '], COUNT(*) FROM [' + TABLE_NAME + '] WHERE [' + column_name + '] = ''' + @SearchString + '''' + ' GROUP BY [' + column_name + ']'
FROM INFORMATION_SCHEMA.COLUMNS
INNER
JOINsys.sysobjects
ONOBJECT_ID(Table_Name) = ID
WHEREDATA_TYPE in ( 'varchar', 'nvarchar', 'Text', 'nText')
ANDxtype = 'U'
ORDER
BYTABLE_NAME, COLUMN_NAME
ELSE
INSERT@SQLqueries
SELECT 'INSERT ##Results (Tablename, ColumnName, SearchValue, Found_Count) SELECT ''' + TABLE_NAME +''' as Tname, ''' + column_name + ''' as Cname, [' + column_name + '], COUNT(*) FROM [' + TABLE_NAME + '] WHERE [' + column_name + '] LIKE ''%' + @SearchString + '%''' + ' GROUP BY [' + column_name + ']'
FROM INFORMATION_SCHEMA.COLUMNS
INNER
JOINsys.sysobjects
ONOBJECT_ID(Table_Name) = ID
WHEREDATA_TYPE in ( 'varchar', 'nvarchar', 'Text', 'nText')
ANDxtype = 'U'
ORDER
BYTABLE_NAME, COLUMN_NAME
ELSE-- FIND INTEGER NUMBERs
IF@SearchType = 'EQUALS'
INSERT@SQLqueries
SELECT 'INSERT ##Results (Tablename, ColumnName, SearchValue, Found_Count) SELECT ''' + TABLE_NAME +''' as Tname, ''' + column_name + ''' as Cname, [' + column_name + '], COUNT(*) FROM [' + TABLE_NAME + '] WHERE [' + column_name + '] = ' + @SearchString + '' + ' GROUP BY [' + column_name + ']'
FROM INFORMATION_SCHEMA.COLUMNS
INNER
JOINsys.sysobjects
ONOBJECT_ID(Table_Name) = ID
WHEREDATA_TYPE in ( 'INT', 'BIT' )
ANDxtype = 'U'
ORDER
BYTABLE_NAME, COLUMN_NAME
ELSE
INSERT@SQLqueries
SELECT 'INSERT ##Results (Tablename, ColumnName, SearchValue, Found_Count) SELECT ''' + TABLE_NAME +''' as Tname, ''' + column_name + ''' as Cname, [' + column_name + '], COUNT(*) FROM [' + TABLE_NAME + '] WHERE CONVERT(VARCHAR(100), [' + column_name + ']) LIKE ''%' + @SearchString + '%''' + ' GROUP BY [' + column_name + ']'
FROM INFORMATION_SCHEMA.COLUMNS
INNER
JOINsys.sysobjects
ONOBJECT_ID(Table_Name) = ID
WHEREDATA_TYPE in ( 'INT', 'BIT' )
ANDxtype = 'U'
ORDER
BYTABLE_NAME, COLUMN_NAME
Select @I = MAX(ID) From @SQLqueries
While@I > 1
BEGIN
Select @SQL = SQLstatement From @SQLqueries Where ID = @I
Exec(@SQL)
SET@I = @I - 1
END
SELECT* From ##Results
--SELECT * From @SQLqueries
DROP TABLE ##Results
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--PROCEDURE END
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply