Search all tables in Database

January 23, 2021

 Create store procedure to search all table in Database

    Search all table in database in sql server
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
CREATE PROC sp_globalSearch @SearchStr NVARCHAR(100)
AS
DECLARE @tbl TABLE (
	mykey INT NULL
	,TableName NVARCHAR(200)
	,ColumnName NVARCHAR(370)
	,ColumnValue NVARCHAR(3630)
	)

SET NOCOUNT ON

DECLARE @TableName NVARCHAR(256)
	,@ColumnName NVARCHAR(128)
	,@SearchStr2 NVARCHAR(110)

SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

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'
						)
					AND QUOTENAME(COLUMN_NAME) > @ColumnName
				)

		IF @ColumnName IS NOT NULL
		BEGIN
			INSERT INTO @tbl
			EXEC ('SELECT 0 as mykey, ''' + @TableName + ''' as TableName,''' + @ColumnName + ''' as ColumnName, LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2)
		END
	END
END

PRINT 'message'

DECLARE @tbl2 TABLE (
	mykey INT NULL
	,TableName NVARCHAR(200)
	,ColumnName NVARCHAR(370)
	,ColumnValue NVARCHAR(3630)
	)
DECLARE @mkey INT;
DECLARE @colname NVARCHAR(max);
DECLARE @tblname NVARCHAR(max);
DECLARE @val NVARCHAR(max);
DECLARE @tempcol NVARCHAR(max);

SET @mkey = 1;

DECLARE mycur CURSOR
FOR
SELECT TableName
	,ColumnName
	,ColumnValue
FROM @tbl

OPEN mycur

FETCH NEXT
FROM mycur
INTO @tblname
	,@colname
	,@val

SET @tempcol = @tblname;

WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@tempcol <> @tblname)
	BEGIN
		SET @mkey = @mkey + 1;
		SET @tempcol = @tblname;
	END

	INSERT INTO @tbl2
	VALUES (
		@mkey
		,@tblname
		,@colname
		,@val
		);

	FETCH NEXT
	FROM mycur
	INTO @tblname
		,@colname
		,@val
END

CLOSE mycur

DEALLOCATE mycur;

SELECT *
FROM @tbl2;
Tags:

Post a Comment

0 Comments