[SQL]SPFINDSTRING

CREATE PROCEDURE [dbo].[SPFINDSTRING]	/*
	by Caipi
	
	1		»	Function
	2		»	Procedure
	4		»	Trigger
	8		»	Table (String)
	16		»	Table (Numeric)
*/

	@SEEK	nvarchar(128),
	@Types	tinyint

as

SET NOCOUNT ON;

IF (LEN(@SEEK) = 0)
	BEGIN
		RAISERROR('Invalid SEEK length', 10, 1);
		RETURN -1;
	END
ELSE IF ((@Types & (1 | 2 | 4 | 8 | 16)) = 0)
	BEGIN
		RAISERROR('Invalid Types', 10, 1);
		RETURN -2;
	END

Declare	@String		nvarchar(max),
		@obj_ID		int,
		@col_ID		int,
		@min_Hits	int,
		@obj_Type	nvarchar(2),
		@obj_Name	nvarchar(128),
		@col_Name	nvarchar(128);
SELECT	@String		= N'',
		@min_Hits	= 0;

Declare	@tbl_POOL		TABLE(ObjectID int, ObjectName nvarchar(128), ObjectType nvarchar(2));
Declare	@tbl_Results	TABLE(ObjectID int, ObjectName nvarchar(128), ObjectType nvarchar(16), Keyword nvarchar(128), ColumnName nvarchar(128) NULL, min_Hits INT, Query nvarchar(max));

INSERT INTO	@tbl_POOL
SELECT		[object_id],
			[name], 
			[type]		FROM sys.objects WHERE [type] in	(	CASE WHEN @Types & 1	!= 0 THEN N'FN'	ELSE N'' END,
																CASE WHEN @Types & 2	!= 0 THEN N'P'	ELSE N'' END,
																CASE WHEN @Types & 4	!= 0 THEN N'TR'	ELSE N'' END,
																CASE WHEN @Types & 8	!= 0 THEN N'U'	ELSE N'' END,
																CASE WHEN @Types & 16	!= 0 THEN N'U'	ELSE N'' END	);

SELECT	@obj_ID = MIN(ObjectID) FROM @tbl_POOL;

while (@obj_ID is not null)
	BEGIN
		SELECT	@obj_Type = ObjectType,
				@obj_Name = ObjectName	FROM @tbl_POOL WHERE ObjectID = @obj_ID;
		
		IF (@obj_Type in (N'FN', N'P', N'TR'))
			BEGIN
				SELECT	@String = N'';
				SELECT	@String = @String + [text] FROM syscomments WHERE id = @obj_ID;
				
				IF (CHARINDEX(@SEEK, @String) > 0)
					BEGIN
						INSERT INTO @tbl_Results	SELECT @obj_ID, @obj_Name,	CASE
																					WHEN @obj_Type = 'FN'	THEN N'Function'
																					WHEN @obj_Type = 'P'	THEN N'Procedure'
																					WHEN @obj_Type = 'TR'	THEN N'Trigger'
																				END, @SEEK, N'', 1, N'';
					END
			END
		ELSE IF (@obj_Type = N'U' AND @Types & 8 != 0)
			BEGIN
				SELECT	@col_ID = MIN(column_id) FROM sys.columns WHERE [object_id] = @obj_ID AND collation_name is not null;
				
				while (@col_ID is not null)
					BEGIN
						SELECT	@col_Name = name FROM sys.columns WHERE column_id = @col_ID AND [object_id] = @obj_ID;
						SELECT	@String = N'';
						SELECT	@String = N'SELECT @Q = COUNT(1) FROM dbo.' + @obj_Name + N' with(NOLOCK) WHERE CHARINDEX(''' + @SEEK + N''', [' + @col_Name + N']) > 0';
						
						exec	sp_executesql @String, N'@Q INT OUTPUT', @min_Hits OUTPUT;
						
						IF (@min_Hits > 0)
							BEGIN
								INSERT INTO @tbl_Results	SELECT @obj_ID, @obj_Name, 'User_Table', @SEEK, @col_Name, @min_Hits, 'SELECT * FROM dbo.' + @obj_Name + ' with(NOLOCK) WHERE CHARINDEX(''' + @SEEK + N''', [' + @col_Name + N']) > 0';
							END
						
						SELECT	@col_ID		= MIN(column_id),
								@col_Name	= NULL,
								@min_Hits	= 0 FROM sys.columns WHERE [object_id] = @obj_ID AND collation_name is not null AND column_id > @col_ID;
					END		
			END
		ELSE IF (@obj_Type = N'U' AND @Types & 16 != 0 AND ISNUMERIC(@SEEK) = 1)
			BEGIN
				SELECT	@col_ID = MIN(column_id) FROM sys.columns WHERE [object_id] = @obj_ID AND system_type_id in (127, 122, 108, 106, 62, 60, 59, 48, 52, 56);
				
				while (@col_ID is not null)
					BEGIN
						SELECT	@col_Name = name FROM sys.columns WHERE column_id = @col_ID AND [object_id] = @obj_ID;
						SELECT	@String = N'';
						SELECT	@String = N'SELECT @Q = COUNT(1) FROM dbo.' + @obj_Name + N' with(NOLOCK) WHERE [' + @col_Name + N'] = ' + @SEEK;
						
						exec	sp_executesql @String, N'@Q INT OUTPUT', @min_Hits OUTPUT;
						
						IF (@min_Hits > 0)
								BEGIN
									INSERT INTO @tbl_Results	SELECT @obj_ID, @obj_Name, 'User_Table', @SEEK, @col_Name, @min_Hits, 'SELECT * FROM dbo.' + @obj_Name + ' with(NOLOCK) WHERE [' + @col_Name + N'] = ' + @SEEK;
								END
							
							SELECT	@col_ID		= MIN(column_id),
									@col_Name	= NULL,
									@min_Hits	= 0 FROM sys.columns WHERE [object_id] = @obj_ID AND system_type_id in (127, 122, 108, 106, 62, 60, 59, 48, 52, 56) AND column_id > @col_ID;						
					END
			END
		
		SELECT	@obj_ID = MIN(ObjectID) FROM @tbl_POOL WHERE ObjectID > @obj_ID;
	END

SELECT * FROM @tbl_Results ORDER BY ObjectType, ObjectID;

SET NOCOUNT OFF;

RETURN 1;

SELECT	ROUTINE_NAME, ROUTINE_DEFINITION, LEN(ROUTINE_DEFINITION) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'Procedure' AND ROUTINE_NAME = '_AddNewChar';

SELECT OBJECT_NAME(id), [text], LEN([text]) FROM syscomments WHERE id = OBJECT_ID('_AddNewChar');