- MS Sql Server
- 264
- September-28-2023
- by Junaid A
Problem:
Sometime, have any value, but we are unable to find, from where this value is displayed. to know what is table name and column name from where this value is beiing displayed.
we have written some ms sql script, that will take paramater as "KeyWords" or Any data like name, or any integer and it will return the table and column name from the selected database.
Search value in database and return table and column name
Solution:
DECLARE @SearchbyStringOrInt nvarchar(100) = 'ST'
DECLARE @RturnResul TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchbyStringOrInt2 nvarchar(110)
SET @TableName = ''
SET @SearchbyStringOrInt2 = QUOTENAME('%' + @SearchbyStringOrInt + '%','''')WHILE @TableName IS NOT NULL
BEGIN
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', 'int', 'decimal', 'ntext', 'xml')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @RturnResul
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT((cast(' + @ColumnName + ' as nvarchar(max))), 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE (cast(' + @ColumnName + ' as nvarchar(max))) LIKE ' + @SearchbyStringOrInt2
)
END
END
ENDSELECT ColumnName, ColumnValue FROM @RturnResul
Search value in ms sql server database
Find table name and coumn name where value is being store.
