Monday 16th of September 2019 10:22:29 PM
Our Services
Select Theme
Back
CREATE PROCEDURE spFindTableNameInAllDatabase @TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)

DECLARE @getDBName CURSOR SET @getDBName = CURSOR
FOR
SELECT NAME
FROM sys.databases

CREATE TABLE #TmpTable (
	DBName VARCHAR(256)
	, SchemaName VARCHAR(256)
	, TableName VARCHAR(256)
	)

OPEN @getDBName

FETCH NEXT
FROM @getDBName
INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @varSQL = 'USE ' + @DBName + '; INSERT INTO #TmpTable SELECT ''' + @DBName + ''' AS DBName, SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables WHERE name LIKE ''%' + @TableName + '%'''

	EXEC (@varSQL)

	FETCH NEXT
	FROM @getDBName
	INTO @DBName
END

CLOSE @getDBName

DEALLOCATE @getDBName

SELECT *
FROM #TmpTable

DROP TABLE #TmpTable GO

EXEC spFindTableNameInAllDatabase 'Table Name' GO
 
     
     
Home | About Us | Products | Services |  Templates | Demo | Contact Us | Careers | Feedback | SiteMap
2005-2014 Logical Technologies. All Rights Reserved.