August 19, 2011 at 1:48 am
If i use the below query to check the data available or not in the table the result print perfect.
IF EXISTS(SELECT 'AC_Cancellation_Master' AS TableName,* FROM [AC_Cancellation_Master] WITH (NOLOCK) WHERE [Canc_Type] like'%Mohamad')
BEGIN
PRINT 'DATA EXISTS'
END
ELSE
BEGIN
PRINT 'DATA NOT EXISTS'
END
But i want to execute the same query in, storing through Variable and execute but it gives an error
DECLARE @sqlquery VARCHAR(4000)
SELECT @sqlquery = N'SELECT' + CHAR(39) + 'AC_Cancellation_Master' + CHAR(39) + 'AS TableName,* FROM [AC_Cancellation_Master] WITH (NOLOCK) WHERE [Canc_Type] like' + CHAR(39) + '%Mohamad' + CHAR(39)
PRINT @sqlquery
IF EXISTS(SP_EXECUTESQL(@SQLQuery))
BEGIN
PRINT 'DATA EXISTS'
END
ELSE
BEGIN
PRINT 'DATA NOT EXISTS'
END
Please help ......
Thanks in Advance
Patel Mohamad
August 19, 2011 at 3:44 am
I'm 99.99% sure you can't do and if (exec) like this.
You could do it with everything dynamic or insert the results to temp table and check for @@rowcount > 0
August 19, 2011 at 5:55 am
Thanks for your Reply...
i found other way, please check it
DECLARE @sqlquery VARCHAR(1000)
SELECT @sqlquery= N'IF EXISTS (SELECT ' + CHAR(39) + 'AC_Cancellation_Master' + CHAR(39) + ' AS TableName FROM [AC_Cancellation_Master] WITH (NOLOCK) WHERE [Canc_Type] like ' + CHAR(39) + '%Mohamad' + CHAR(39) + ')'
+ 'SELECT ' + CHAR(39) + 'AC_Cancellation_Master' + CHAR(39) + ' AS TableName,* FROM [AC_Cancellation_Master] WITH (NOLOCK) WHERE [Canc_Type] like' + CHAR(39) + '%Mohamad' + CHAR(39)
PRINT @sqlquery
EXEC SP_EXECUTESQL @sqlquery
Patel Mohamad
August 19, 2011 at 6:15 am
What's wrong with returning and empty resultset if there's no data?
August 19, 2011 at 6:43 am
I was looking for solution which gives me the details of the table and column which contains respective data,
Just go through the code
SET NOCOUNT ON;
DECLARE @indexRow INT,
@rowCount int,
@sqlquery NVARCHAR(4000),
@Value VARCHAR(100),
@indexCol INT,
@ColumnCount int,
@tableName VARCHAR(100),
@columnName VARCHAR(100)
SELECT @Value = '%Mohamad%'-- Add your text here with or without % sign
BEGIN --0.01
IF LEFT(@Value ,1) = '%' OR RIGHT(@Value ,1) = '%'
SELECT @Value = @Value
ELSE
SELECT @Value = '%' + @Value
END --0.01
SELECT Identity(INT,1,1) AS Sr_No, SYSOBJECTS.id, SYSOBJECTS.Name INTO #Temp_Table_Row
FROM SYSOBJECTS WITH (NOLOCK) INNER JOIN SYSindexes WITH (NOLOCK) ON
SYSOBJECTS.ID = SYSindexes.ID
WHERE SYSOBJECTS.xtype = 'U'
AND SYSindexes.Rowcnt >0 ORDER BY SYSOBJECTS.Name
SELECT @rowCount = @@RowCount , @indexRow = 1
WHILE @indexRow <= @rowCount
BEGIN--- 1.0
IF EXISTS(SELECT Sr_No FROM #Temp_Table_Row WITH (NOLOCK)WHERE Sr_No = @indexRow)
BEGIN-- 1.1
SELECT Identity(INT,1,1) AS Sr_No, ID, Name, xtype INTO #Temp_Table_Col
FROM Syscolumns WITH (NOLOCK)
WHERE ID = (SELECT ID FROM #Temp_Table_Row WITH (NOLOCK) WHERE Sr_No = @indexRow) --@ID_Row
and xtype in (35 ,98 ,165 ,167 ,173 ,175 ,231 ,231 ,239) -- ,99 For datatypes char varhcar etc
ORDER BY name
SELECT @ColumnCount = @@RowCount, @indexCol = 1
WHILE @indexCol <= @ColumnCount
BEGIN-- 2.0
IF EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @indexCol)
BEGIN-- 2.1
SELECT @tableName = Object_Name(ID) ,@columnName = Name FROM #Temp_Table_Col WHERE Sr_No = @indexCol
SELECT @sqlquery= N'IF EXISTS (SELECT ' + CHAR(39) + @columnName + CHAR(39)
+ ' FROM [' + @tableName + '] WITH (NOLOCK) WHERE [' + @columnName + '] LIKE ' + CHAR(39) + @Value + CHAR(39) + ')'
+ 'SELECT ' + CHAR(39) + @tableName + CHAR(39) + ' AS TableName,' + CHAR(39) + @columnName + CHAR(39)
+ ' AS ColumnName, * FROM [' + @tableName
+ '] WITH (NOLOCK) WHERE [' + @columnName + '] LIKE ' + CHAR(39) + @Value + CHAR(39)
EXEC SP_EXECUTESQL @sqlquery
END-- 2.1
SELECT @indexCol = @indexCol + 1
END-- 2.0
DROP TABLE #Temp_Table_Col
END -- 1.1
SELECT @indexRow = @indexRow + 1
END -- 1.0
DROP TABLE #Temp_Table_Row
SET NOCOUNT OFF;
GO
Patel Mohamad
August 19, 2011 at 6:44 am
hope you will get my point
Patel Mohamad
August 19, 2011 at 7:06 am
So basically search all tables / columns for @x.
You can search this site in the script section. This has been done many times over. I just don't have 1 to recommend.
August 19, 2011 at 7:07 am
You can also use a similar technic. This inserts the results in a temp table so the "empty" datasets are ignored.
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON
GO
USE [master]
GO
IF OBJECT_ID('dbo.spaceused', 'U') IS NULL
BEGIN
CREATE TABLE dbo.spaceused (
DbName sysname DEFAULT(''),
tblName sysname,
Row_count INT ,
Reserved VARCHAR(50),
data VARCHAR(50) ,
index_size VARCHAR(50),
unused VARCHAR(50),
PRIMARY KEY CLUSTERED (DbName, tblName)
);
END
ELSE
BEGIN
--DROP TABLE dbo.spaceused
TRUNCATE TABLE dbo.spaceused
END
COMMIT
GO
DECLARE @Cmd VARCHAR(8000)
SET @Cmd = 'USE [?];
IF ''?'' NOT IN (''tempdb''
--, ''master'', ''model'', ''msdb''
)
BEGIN
--PRINT ''?''
DECLARE @InnerCmd VARCHAR(8000)
SET @InnerCmd = ''
EXEC sp_spaceused '''''' + CHAR(63) + ''''''''
INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)
EXEC sp_MSforeachtable @InnerCmd
UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''
END
'
--PRINT @Cmd
EXEC sp_MSforeachdb @Cmd
DELETE FROM dbo.spaceused WHERE Row_count = 0
SELECT
DbName
, tblName
, Row_count
, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved
, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data
, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size
, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused
FROM
dbo.spaceused
ORDER BY
DbName
, MB_Reserved DESC
, Row_count DESC
COMMIT
August 19, 2011 at 9:02 am
Here is my version from several years ago...warning this type of searching is PAINFULLY SLOW!!!!!!!! I would strongly recommend not running this type of sql during normal business hours. Yours is slow too, it is just the nature of the beast with these things. I know Lowell has one that is somewhat different than mine. He may drop by and post his too.
declare @table_name varchar(2000)
declare @sSQL nvarchar(4000)
declare @result varchar(20)
declare @column_name varchar(2000)
declare @SearchVal varchar(200)
set @SearchVal = '%your search val here%'
declare @ColName varchar (250)
set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'
declare SearchList cursor for
select distinct so.name,sc.name from syscolumns sc
inner join sysobjects so on sc.id = so.id
where sc.name like @ColName
and so.type = 'U'
open SearchList
fetch next from SearchList into @table_name, @column_name
while(@@fetch_status = 0)
begin
select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''
exec sp_executesql @sSQL
--select @ssql
fetch next from SearchList into @table_name, @column_name
end
close SearchList
deallocate SearchList
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2011 at 11:44 pm
Thanks sean,
Agree with you,
my solution is little bit slow... but my question is to search a respective data in any of the column of the database tables.
Hope i will get a faster solution!!!!
Thanks in advance
Patel Mohamad
August 22, 2011 at 7:28 am
You really can't find a faster solution. I just wanted to make sure to warn you about it completely destroying your server before you ran it your production server during business hours. It is slow but the very nature of what you are trying to do is slow. Unfortunately it is something that can't always be avoided. Hope you found your needle in the haystack. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 24, 2011 at 12:58 am
Hi Sean,
Thanks for your valuable suggestion, and i have implemented and search other things and discovered
Please have a look and suggest if i am missing something...
SET NOCOUNT ON;
DECLARE @indexRow INT,
@rowCount int,
@sqlquery NVARCHAR(4000),
@Value VARCHAR(100),
@tableName VARCHAR(100),
@columnName VARCHAR(100)
SELECT @Value = '%Mohamad%'-- Add your text here with or without % sign
BEGIN --0.01
IF LEFT(@Value ,1) = '%' OR RIGHT(@Value ,1) = '%'
SELECT @Value = @Value
ELSE
SELECT @Value = '%' + @Value
END --0.01
SELECT
DISTINCT
sysobjects.id, sysobjects.name AS TableName, syscolumns.name AS ColumnName
INTO #Temp_Table
FROM
sysobjects INNER JOIN
sysindexes ON sysobjects.id = sysindexes.id INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id
WHERE
(sysobjects.xtype = 'U')
AND (sysindexes.rowcnt > 0)
AND (syscolumns.xtype IN (35, 98, 165, 167, 173, 175, 231, 231, 239))
AND (syscolumns.length) >= LEN(@Value)
ORDER BY sysobjects.name, syscolumns.name
SELECT Identity(INT,1,1) AS Sr_No, id, TableName, ColumnName
INTO #FinalTable
FROM #Temp_Table ORDER BY TableName, ColumnName
SELECT @rowCount = @@RowCount , @indexRow = 1
DROP TABLE #Temp_Table
WHILE @indexRow <= @rowCount
BEGIN--- 1.0
PRINT @indexRow
IF EXISTS(SELECT Sr_No FROM #FinalTable WITH (NOLOCK)WHERE Sr_No = @indexRow)
BEGIN-- 1.1
SELECT @tableName = TableName ,@columnName = ColumnName FROM #FinalTable WHERE Sr_No = @indexRow
SELECT @sqlquery= N'IF EXISTS (SELECT ' + @columnName +
+ ' FROM [' + @tableName + '] WITH (NOLOCK) WHERE [' + @columnName + '] LIKE ' + CHAR(39) + @Value + CHAR(39) + ')' + CHAR(13)
+ 'SELECT ' + CHAR(39) + @tableName + CHAR(39) + ' AS TableName,' + CHAR(39) + @columnName + CHAR(39)
+ ' AS ColumnName, * FROM [' + @tableName
+ '] WITH (NOLOCK) WHERE [' + @columnName + '] LIKE ' + CHAR(39) + @Value + CHAR(39)
EXEC SP_EXECUTESQL @sqlquery
END -- 1.1
SELECT @indexRow = @indexRow + 1
END -- 1.0
DROP TABLE #FinalTable
SET NOCOUNT OFF;
GO
Thanks
Patel Mohamad
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply