An excellent way of scanning a database [Every Table, Every Column] for any Value. As I’m sure you can imagine, this is incredibly useful for a developer, especially those who have to support existing applications…
This has been wrapped into a stored procedure, This can be used to identify a particular value existing in the whole database. Interesting isnt it ?
This can be of great power if directly given to the user–and possibly of great destruction. Remember, not everyone should be priveleged to all information.
Now, for those ambitious persons out there, this information can be augmented with additional useful information–Perhaps the value of the Primary Key, or a SQL Statement to query that table/column/value, etc. Good luck
(
@Value VARCHAR(64)
)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
DECLARE @TableName VARCHAR(64)
DECLARE @ColumnName VARCHAR(64)
CREATE TABLE #Results
(
TableName VARCHAR(64)
, ColumnName VARCHAR(64)
)
DECLARE TABLES CURSOR
FOR
SELECT
o.name
, c.name
FROM syscolumns c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.type = ‘U’
AND c.xtype IN (167, 175, 231, 239)
ORDER BY
o.name
, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @TableName
, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘IF EXISTS(SELECT NULL FROM [‘ + @TableName + ‘] ‘
SET @sql = @sql + ‘WHERE RTRIM(LTRIM([‘ + @ColumnName + ‘])) LIKE ”%’ + @value + ‘%”) ‘
SET @sql = @sql + ‘INSERT INTO #Results ( TableName, ColumnName ) VALUES (”’+ @TableName + ”’, ”’
SET @sql = @sql + @ColumnName + ”’)’
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @TableName
, @ColumnName
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT *
FROM #Results
DROP TABLE #Results
END
for ease here is the sp for you ,execute and just pass the parameters
/****** Object: StoredProcedure [dbo].[FindMatchingValueInAnyTable] Script Date: 07/16/2009 15:14:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[FindMatchingValueInAnyTable]
(
@Value VARCHAR(64)
)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
DECLARE @TableName VARCHAR(64)
DECLARE @ColumnName VARCHAR(64)
CREATE TABLE #Results
(
TableName VARCHAR(64)
, ColumnName VARCHAR(64)
)
DECLARE TABLES CURSOR
FOR
SELECT
o.name
, c.name
FROM syscolumns c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.type = ‘U’
AND c.xtype IN (167, 175, 231, 239)
ORDER BY
o.name
, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @TableName
, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘IF EXISTS(SELECT NULL FROM [‘ + @TableName + ‘] ‘
SET @sql = @sql + ‘WHERE RTRIM(LTRIM([‘ + @ColumnName + ‘])) LIKE ”%’ + @value + ‘%”) ‘
SET @sql = @sql + ‘INSERT INTO #Results ( TableName, ColumnName ) VALUES (”’ + @TableName + ”’, ”’
SET @sql = @sql + @ColumnName + ”’)’
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @TableName
, @ColumnName
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT *
FROM #Results
DROP TABLE #Results
END
Dear All, Please help for the below mentioned problem.
I have a table, in that table there is some rows contains sql queries, but when i fetched the sql and try execute through asp, then query is not executed.
try to check for the credentials supplied.. and post the errors i ll ping you bac
Hi People,
I wanna know how to create this procedure, execute and the parameters that are to be passed while executing the procedure.
Im quite new to this and I have a requirement of getting the tables with columns using the value as the input.
Thanks!!