March 14, 2016 at 3:06 pm
Hi All,
I am really struggling with the following requirement, I thought it would be quite simple.... but I was wrong!
I need to produce a list of databases that exist on multiple servers that contain a specific table. Then take that list and insert the list of database names into a table on a another server for a centralized reporting purpose.
I have managed to get this working locally on each server using a stored procedure which uses the following syntax as the part of the query that finds the table names 'WHEN state_desc = 'ONLINE' THEN OBJECT_ID( QUOTENAME( name )+ '.[dbo].[mytablen ame]','U' ) END IS NOT NULL;. (the code is an example that is pretty close to what I am using but I have extended it to include linked servers, but do not have access to it from home so will post tomorrow.
The example below is how I found out about this. I was going to use sp_MSForEachDB but have seen issues with inaccurate results.
SELECT name
FROM sys.databases
WHERE CASE
WHEN state_desc = 'ONLINE' THEN OBJECT_ID( QUOTENAME( name ) + '.[dbo].[mytablename]','U' )
END IS NOT NULL;
My stored procedure works fine locally but when I try running against a linked server I don't get any results at all and the only thing I have found is a problem with using "object_id" over a linked server or potentially a problem with accessing the system catalogs.
The user account I am using for the linked server currently has full sysadmin rights so that shouldn't be an issue and I will look at locking it down after.
As always any help would be gratefully received.
Adam
March 15, 2016 at 8:14 am
Hi All,
I think I have this working but it seems really messy, any suggestions?
USE [My_Services]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Event_Table_Extract_List_Databases]
@LnkSQLServer VARCHAR(50)
AS BEGIN
DECLARE @LnkSQLCMD NVARCHAR(1000)
DECLARE @LnkSQLSrv NVARCHAR(1000)
DECLARE @DBState NVARCHAR(10)
SET @DBSTATE = '''ONLINE'''
-- Create a table to hold the sanitized database names - Run once to set the table up and create the stored procedure then comment out afterwards.
--USE [My_Services]
--GO
--/****** Object: Table [dbo].[tblDBList] Script Date: 15/03/2016 14:04:40 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--SET ANSI_PADDING ON
--GO
--CREATE TABLE [dbo].[tblDBList](
-- [Server_Name] [varchar](255) NULL,
-- [DB_Name] [varchar](255) NULL
--) ON [PRIMARY]
--GO
--SET ANSI_PADDING OFF
--GO
-- Create a temp table to store the list of databases ID's and Names that have the "Event" table.
CREATE TABLE #tblTMPDBList
(
Server_Name varchar(255),
Database_Name varchar (255)
)
-- Enable RPC for the linked serevr.
EXEC sp_serveroption @server=@LnkSQLServer, @optname='rpc', @optvalue='true'
EXEC sp_serveroption @server=@LnkSQLServer, @optname='rpc out', @optvalue='true'
-- Search and insert the Server name, database ID and database name for all databases that have a "Event" table.
SELECT @LnkSQLCMD =
N'SELECT @@SERVERNAME ''Server_Name'', name
FROM ' + '[' +@LnkSQLServer +']' + '.master.sys.databases
WHERE CASE
WHEN state_desc = ' + @DBState +
' THEN OBJECT_ID(QUOTENAME(name) + ''.[dbo].[Event]'', ''U'')
END IS NOT NULL'
-- Setup the query to run on the linked server
SELECT @LnkSQLSrv = '[' +@LnkSQLServer +']' + '.tempdb.sys.sp_executesql'
-- Insert the data using the linked server and query variables.
INSERT INTO #tblTMPDBList (Server_Name, Database_Name)
EXEC @LnkSQLSrv @LnkSQLCMD
-- Disable RPC to secure the server.
EXEC sp_serveroption @server=@LnkSQLServer, @optname='rpc', @optvalue='false'
EXEC sp_serveroption @server=@LnkSQLServer, @optname='rpc out', @optvalue='false'
-- Insert new databases into table tblDBList
INSERT INTO tblDBList (Server_Name, DBName)
SELECT DISTINCT tmp.Server_Name, tmp.Database_Name
FROM #tblTMPDBList tmp
WHERE NOT EXISTS
(
SELECT *
FROM tblDBList
WHERE Server_Name = tmp.Server_Name and DBName = tmp.Database_Name
)
-- Delete database entries in table tblDBList that do not exist anymore
DELETE FROM tblDBList
WHERE NOT EXISTS
(
SELECT *
FROM #tblTMPDBList
WHERE Server_Name = tblDBList.Server_Name and Database_Name = tblDBList.DBName
)
-- Display the results.
SELECT Server_Name, DBName
FROM tblDBList
-- Drop the temp table to tidy up afterwards.
DROP TABLE #tblTMPDBList
END
-- Debug section.
-- EXEC [dbo].[usp_Event_Table_Extract_List_Databases] 'MyServer'
-- SELECT @LnkSQLCMD
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply