Linked Server/Use Database/SP Syntax

[briangriffin]

Server 1: OK-DR01, contains two databases (testNdb and TestFdb) that I need to combine query results.

Server 2: OSUMC_BI, OK-DR01 is a linked server.

This query runs fine and returns the data I need when running from OK-DR01:

use Testndb

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowTotal] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowTotal])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;


declare @OSUTableStatus table(DatabaseName varchar(12),TableName varchar(128), RowTotal int)

insert into @OSUTableStatus (DatabaseName, TableName, RowTotal)
	select 'TestNdb', substring(TableName,8,LEN(TableName) - 8), RowTotal from @TableRowCounts
	
delete from @TableRowCounts


use Testfdb

INSERT INTO @TableRowCounts ([TableName], [RowTotal])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;

insert into @OSUTableStatus (DatabaseName, TableName, RowTotal)
	select 'TestFdb', substring(TableName,8,LEN(TableName) - 8), RowTotal from @TableRowCounts
	

use Testndb
declare @TableUpdateTime table (TableName varchar(128), LastUpdate smalldatetime)

insert into @TableUpdateTime

SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
 FROM sys.dm_db_index_usage_stats
 WHERE database_id = DB_ID( 'Testndb') order by 1
 
 use Testfdb
 
 insert into @TableUpdateTime

SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
 FROM sys.dm_db_index_usage_stats
 WHERE database_id = DB_ID( 'Testfdb')
 

select a.*, b.LastUpdate from @OSUTableStatus a left outer join @TableUpdateTime b
	on a.TableName = b.TableName
	
order by 2

So I wanted to create this as a stored procedure on OSUMC_BI, but can't figure out how to fully qualify it - adding server name to use statement doesn't seem to work.

Second choice was to compile it on the OK-DR01 server, but then I realized that I can't use a use statement in a stored procedure.

How can I modify this to be able to compile as a SP on either server? TIA

 

[djj55]

Just re-wrote this today.
Does

IF OBJECT_ID('tempdb..#TableList') IS NOT NULL
    DROP TABLE #TableList;

CREATE TABLE #TableList(
    DatabaseName VARCHAR(100),
    TableName VARCHAR(100),
    CreateDate DATETIME,
    ModDate DATETIME 
);
EXECUTE sp_MSForEachdB 
        'IF ''?'' NOT IN (''MyDB1'', ''MyDB2'', ''MyDB3'') RETURN;
        USE [?];
        INSERT INTO #TableList 
        select 
            ''?'' AS DatabaseName 
            , name as TableName 
            , create_date 
            , modify_date 
        from sys.tables where type = ''U'' 
        ORDER BY modify_date DESC; '
        
SELECT * FROM #TableList
ORDER BY 1, 2

work in a stored procedure?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[briangriffin]

Still bogged down in another urgent project, but didn't want to seem ungrateful. Quickly running this gives me 'can't find sp_MSForEachdB', but I'll work with it and report back, stay tuned. Thanks.