Cross database dependencies on sql 2005

SQL Server Administration
1

is there a way to get cross database dependencies on 2005 the new script will not work on 2005

2

Without seeing the script and the error messages, it's impossible to say.

3

here is the script the errors are that the system tables you access in 2014 do not exist in 2005
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;

4

This article https://technet.microsoft.com/en-us/library/ms177862(v=sql.90).aspx

System Views (Transact-SQL)

Other Versions
Microsoft SQL Server 2005 provides the following collections of system views that expose metadata:

should help

5

Pretty sure the old name for that was dbo.sysdatabases

Similar names existed for other system tables that are now sys.SomeName

Before that relying on varchar with default size is not safe. In some circumstances SQL will set the size to 1 byte ... "20" is a sensible maximum size for numbers (converted to strings), and "an easy to remember round number" ... well, that's why we use it for all numeric-to-string conversions!

Personally I would not use varchar(MAX) for anything that not explicitly need more than 8000 characters (or 4000 for NVarchar). It is slower to process, because data held out-of-row, and SQL will pre-allocate large amounts of memory in the expectation that large amounts of data is likely to show up!

6

sys.sql_expression_dependencies does not exist in SQL 2005.
Instead, you have to use the older -- and less useful -- sys.sql_dependencies.

sys.databases is fine in 2005, and you should definitely continue using it.

7

Shows now useless my memory is :frowning: