USE process1C;

IF object_id(N'tempdb..#ReferenceList',N'U') IS NOT NULL DROP TABLE #ReferenceList
CREATE TABLE #ReferenceList
	(ReferenceTbName VARCHAR(30));

INSERT INTO #ReferenceList
SELECT TABLE_NAME FROM empty_base.INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME LIKE '_Reference[0-9]%' AND TABLE_NAME NOT LIKE '%[0-9]_VT[0-9]%'


USE empty_base;

DECLARE
	@SearchTextBinary binary(16),
	@SearchText VARCHAR(max),
	@SQLString nvarchar(400),
	@CurrentTable nvarchar(100),
	@SubCount nvarchar(max);

SET @SearchTextBinary = 0xBD55BC855654353211E8D912F34E1AED
SET @SearchText = CONVERT(varchar(max), @SearchTextBinary, 1)

DECLARE CursorSearch CURSOR FAST_FORWARD
		FOR SELECT ReferenceTbName FROM #ReferenceList;
OPEN CursorSearch

FETCH NEXT FROM CursorSearch
	INTO @CurrentTable
	WHILE @@FETCH_STATUS=0

BEGIN

SET @SQLString = 
'IF EXISTS
	(SELECT * FROM @CurrentTable WHERE _IDRRef = @SearchText )
BEGIN
	SELECT TOP (1)  @SubCount = _Description FROM @CurrentTable WHERE _IDRRef = @SearchText 

END'

	IF	COL_LENGTH(@CurrentTable,'_Description') > 0
		EXECUTE sp_executesql @SQLString, N'@CurrentTable nvarchar (100), @SearchText VARCHAR(max), @SubCount nvarchar(max) OUTPUT', @CurrentTable, @SearchText, @SubCount OUTPUT;

		FETCH NEXT FROM CursorSearch INTO @CurrentTable
END

CLOSE CursorSearch;
DEALLOCATE CursorSearch;

Print @SubCount;

'IF EXISTS
	(SELECT * FROM ' + quotename(@CurrentTable) + ' WHERE _IDRRef = @SearchText )
BEGIN
	SELECT TOP (1)  @SubCount = _Description FROM ' + quotename(@CurrentTable) + ' WHERE _IDRRef = @SearchText 

END'