RSS
 

Posts Tagged ‘search views’

SQL Script to search a string in all tables’ or views’ data

21 Dec

Here you are a very useful script to search a string in all data of tables or views.

You can use this script to find out a parameter stored in which tables.

It is also useful to learn which tables are used for some string in complex repositories as Informatica Repository DB.

USE DB_TO_SEARCH
 
DECLARE
@KW VARCHAR(100) = 'STRING_TO_SEARCH',
@SQL VARCHAR(MAX) = ''
 
SELECT @SQL += 'IF EXISTS(SELECT * FROM ' + SCHEMA_NAME(t.schema_id) + '.' + T.name +
' WHERE CONVERT(VARCHAR(MAX), ' + c.name + ') LIKE ''%' + @KW + '%'')' + CHAR(10) +
'PRINT ''TABLE NAME:' + SCHEMA_NAME(T.schema_id) + '.' + t.name + '~COLUMN NAME:' +
c.name + '''' + CHAR(10)
FROM
sys.COLUMNS c
INNER JOIN sys.TABLES t --Use "sys.views" to seach views instead of tables
ON c.object_id = t.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id AND c.user_type_id = ty.user_type_id
WHERE ty.name LIKE '%char%' OR ty.name LIKE '%text%' --You can also change types of columns to search
 
EXEC(@SQL)

The output of the script as;

TABLE NAME:dbo.OPB_DTL_SWIDG_LOG~COLUMN NAME:LOCATION_NAME
TABLE NAME:dbo.OPB_SWIDGET_ATTR~COLUMN NAME:ATTR_VALUE

Please check the article SQL Script to search objects in all databases to search a string in objects, not data.

 
No Comments

Posted in English, SQL Server

 

SQL Script to search objects in all databases

16 Jun

Here you are another simple and very useful SQL script to search any object in all databases on the server.

Script searches all tables, views, stored procedures and functions of all databases.

SET NOCOUNT ON
 
DECLARE @DB VARCHAR(40), @CMD VARCHAR(1000), @OBJECT_NAME VARCHAR(100)
 
SET @OBJECT_NAME = 'MIS_MASRKOM_PARAM'
 
DECLARE DBNAME CURSOR
FOR SELECT NAME FROM SYS.DATABASES ORDER BY 1
 
OPEN DBNAME
FETCH NEXT FROM DBNAME INTO @DB
WHILE @@FETCH_STATUS = 0
 BEGIN
 
  IF @DB NOT IN
            ('MASTER', 'MSDB', 'MODEL', 'TEMPDB', 'DECISION_MODULE', 'PUBS', 'NORTHWIND')
 
      BEGIN
            SELECT @CMD =
                  'USE [' + @DB + ']
                  SELECT DISTINCT ''' + RTRIM(@DB) + ''' AS ' + RTRIM(@DB) + ', SO.NAME, SO.TYPE
                  FROM SYS.SYSOBJECTS SO (NOLOCK) JOIN SYS.SYSCOMMENTS SC (NOLOCK) ON SC.ID=SO.ID
                  WHERE SC.TEXT LIKE ''%' + @OBJECT_NAME + '%''
                  ORDER BY 2, 3'
            PRINT RTRIM(@CMD)
            EXEC(@CMD)
      END
 
  FETCH NEXT FROM DBNAME INTO @DB
 END
DEALLOCATE DBNAME
 
1 Comment

Posted in English, SQL Server