finding cross dependent databases in 2005

Question

Post reply

finding cross dependent databases in 2005

myukas

SSC Eights!

Points: 860
More actions
November 30, 2017 at 10:56 am

#409816

i have a script to find cross dependent databases in 2008 and 2014 will not work in 2005 is there an easy way to do in 2005

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

One, what do you mean by doesn't work.
Two, without see the code really have no idea.

myukas SSC Eights! Points: 860 More actions · Answer 2

here is my code that works on 2014
CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS

SET NOCOUNT ON;

CREATE TABLE #databases(
database_id int,
database_name sysname
);

INSERT INTO #databases(database_id, database_name)
SELECT database_id, [name]
FROM sys.databases
WHERE 1 = 1
AND [state] <> 6 /* ignore offline DBs */
AND database_id > 4; /* ignore system DBs */

DECLARE
@database_id int,
@database_name sysname,
@sql varchar(max);

CREATE TABLE #dependencies(
referencing_database varchar(max),
referencing_schema varchar(max),
referencing_object_name varchar(max),
referenced_server varchar(max),
referenced_database varchar(max),
referenced_schema varchar(max),
referenced_object_name varchar(max)
);

WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
SELECT TOP 1 @database_id = database_id,
@database_name = database_name
FROM #databases;

SET @sql = 'INSERT INTO #dependencies select
   DB_NAME(' + convert(varchar,@database_id) + '),
   OBJECT_SCHEMA_NAME(referencing_id,'
    + convert(varchar,@database_id) +'),
   OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '),
   referenced_server_name,
   ISNULL(referenced_database_name, db_name('
     + convert(varchar,@database_id) + ')),
   referenced_schema_name,
   referenced_entity_name
FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';

EXEC(@sql);

DELETE FROM #databases WHERE database_id = @database_id;
END;

SET NOCOUNT OFF;

SELECT * FROM #dependencies;

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

November 30, 2017 at 11:05 am

#1970113

Again, what do you mean by doesn't work.

myukas SSC Eights! Points: 860 More actions · Answer 4

i get objects not found because the system tables for this in 2014 do not exist in 2005

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I'll have to do some research, but not sure if there is anything that will work similarly in SQL Server 2005. Plus, I don't have a SQL Server 2005 instance that I could look at either.
However, I am not a fan of using WHILE loops and temporary tables to replace cursors where they are appropriate so I took the time to rewrite your procedure they way I prefer. That also includes eliminating the use of MAX data types where they really aren't needed.

CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS SET NOCOUNT ON;

DECLARE
@database_id     INT
, @database_name sysname
, @sql           NVARCHAR(MAX)
, @sqlparm       NVARCHAR(MAX) = N'@db_id INT';

CREATE TABLE [#dependencies] (
[referencing_database]      NVARCHAR(256)
, [referencing_schema]      NVARCHAR(256)
, [referencing_object_name] NVARCHAR(256)
, [referenced_server]       NVARCHAR(256)
, [referenced_database]     NVARCHAR(256)
, [referenced_schema]       NVARCHAR(256)
, [referenced_object_name] NVARCHAR(256)
);

DECLARE DBNames CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
[name]
, [database_id]
FROM
[sys].[databases]
WHERE
[state] <> 6 /* ignore offline DBs */
AND [database_id] > 4; /* ignore system DBs */

OPEN [DBNames];

FETCH NEXT FROM [DBNames]
INTO @database_name, @database_id;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =
N'INSERT INTO #dependencies select
DB_NAME(@db_id),
OBJECT_SCHEMA_NAME(referencing_id,@db_id),
OBJECT_NAME(referencing_id,@db_id),
referenced_server_name,
ISNULL(referenced_database_name, db_name(@db_id)),
referenced_schema_name,
referenced_entity_name
FROM ' + QUOTENAME(@database_name) + '.sys.sql_expression_dependencies';

EXEC [sys].[sp_executesql] @sql, @sqlparm, @db_id = @database_id;

FETCH NEXT FROM [DBNames]
INTO @database_name, @database_id;
END;

CLOSE [DBNames];

DEALLOCATE [DBNames];

SET NOCOUNT OFF;

SELECT * FROM [#dependencies];
GO -- End batch creating stored procedure