February 2, 2015 at 2:25 pm
I have multiple linked servers and trying to find if a table (approximate table name) exists in one of the linked servers.
I have a code to find it in local DB....how can I modify to include all linked servers as well:
ALTER PROCEDURE [dbo].[FindTable]
@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
February 4, 2015 at 8:07 am
How about something like this? I haven't tested it, but it might be a good place to start.
DECLARE @tableName VARCHAR(256) = 'dev'
DECLARE @serverName NVARCHAR(250)
DECLARE @sqlQry NVARCHAR(2000) = N'''EXEC sp_msforeachdb ''''USE ?; INSERT INTO #TmpTable SELECT ''''?'''', SCHEMA_NAME(schema_id), name FROM sys.tables WHERE name LIKE ''''%' + @tableName + N'%'''''''
DECLARE @servers TABLE (serverName NVARCHAR(250), srv_providername NVARCHAR(128) NULL, srv_product NVARCHAR(128) NULL, srv_datasource NVARCHAR(4000) NULL,
srv_providerstring NVARCHAR(4000) NULL, srv_location NVARCHAR(4000) NULL, srv_cat sysname NULL)
INSERT INTO @servers
EXEC sp_linkedservers
-- Include the local server?
INSERT INTO @servers (serverName) VALUES (@@SERVERNAME)
CREATE TABLE #TmpTable (DBName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256))
DECLARE getServerName CURSOR LOCAL FORWARD_ONLY FOR
SELECT serverName FROM @servers
OPEN getServerName
FETCH NEXT FROM getServerName INTO @serverName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @innerQry NVARCHAR(2000) = N'EXEC (' + @sqlQry + N') AT [' + @serverName + N']'
PRINT @innerQry
EXEC (@innerQry)
FETCH NEXT FROM getServerName INTO @serverName
END
CLOSE getServerName
DEALLOCATE getServerName
SELECT * FROM #TmpTable
DROP TABLE #TmpTable
February 4, 2015 at 8:14 am
Please tell me this isn't something you plan on doing on a regular basis. The performance of something like this is going to be horrendous. If this is just analysis for figuring it out I would suggest just looking through the databases on your servers. Given that you aren't sure what database or server a given table belongs to it might be good to spend this time anyway so you are more familiar with the system.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 4, 2015 at 8:53 am
Thanks dlaughlin 24148...will test it out and let you know.
No this is not something I will do on a regular basis...want to develop this so that I won't have to go through each single database in servers as there are many to go through for just one table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply