SQL Server Version - 2008 R2
I am working on evaluating a DMS solution, with an objective of taking over maintenance. The original solution has one central database, that has data pertaining to the manufacturer. It also has one database for each dealer, which means there are a lot of cross database dependencies.
The problems:
- No DB documentation
- No code comments
- Lots of heaps
- No standard object naming conventions
- The central DB has 460+ tables and 900+ SProcs, in addition to other
objects
- Each dealer DB has 370+ tables and 2350+ SProcs, in addition to other
objects
As a first step, I am recommending a complete clean-up of the DB, for which it is critical to understand object dependencies, including cross database dependencies. I tried using Red Gate's solution, but the output is way too voluminous. All I want is a list of objects in the databases that do not have any dependencies - they neither depend on other objects, nor are there any objects that depend on them.
Here is the script I have used to get a list of dependencies:
SELECT
DB_NAME() referencing_database_name,
OBJECT_NAME (referencing_id) referencing_entity_name,
ISNULL(referenced_schema_name,'dbo') referenced_schema_name,
referenced_entity_name,
ao.type_desc referenced_entity_type,
ISNULL(referenced_database_name,DB_NAME()) referenced_database_name
FROM sys.sql_expression_dependencies sed
JOIN sys.all_objects ao
ON sed.referenced_entity_name = ao.name
I will be creating a table - Dependencies - into which I will be inserting this result set from each DB. As a next step, I will also be creating another table - AllObjects- which will contain a list of all objects in the Databases. Here is the script to do this:
SELECT
DB_NAME() DBName,
name,
type_desc
FROM sys.all_objects
WHERE type_desc IN
(
'VIEW',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_STORED_PROCEDURE',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'USER_TABLE',
'SQL_SCALAR_FUNCTION'
)
Now, a list of name from this table, that do not appear in the referenced_entity_name column in the dependencies table should give a list of objects that I am looking for.
SELECT
AO.DBName,
AO.name,
AO.type_desc
FROM AllObjects AO
LEFT OUTER JOIN Dependencies D ON
D.referenced_database_name = AO.DBName AND
D.referenced_entity_name = AO.name AND
D.referenced_entity_type = AO.type_desc
WHERE
D.referenced_database_name IS NULL AND
D.referenced_entity_name IS NULL AND
D.referenced_entity_type IS NULL
Now the questions:
- Some object dependencies seem to be missing in the output. What am I
missing?
- How do I validate that my findings are correct?
- I mean is there a different way to do this, so I can compare the
results and double check?
Thanks in advance,
Raj
您可以将结果进行比较，以下脚本发现的人。 下面是完整的文章
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;
哦，MS在检测与sys.sql_expression_dependencies跨数据库的依赖关系取得了不错的努力，但我已经看到了以前错过的东西。 在你的情况，我会找一个丢失的依赖的一个例子，并开始回溯：你从查询放弃了它一些如何？ 如果是这样，解决您的查询。 是否sys.sql_expression_dependencies忽略某一类的依赖？ 在什么情况下？ 是动态的SQL惹的祸？ 等等
你也应该运行sp_refreshsqlmodule在sys.sql_modules每个对象，然后重新运行你的代码。 它迫使SQL Server来刷新依赖信息（在力所能及的范围内）。
现在，为了验证，建立一个跟踪和监听事件114，“审核架构对象访问事件”，再加上起点和存储过程和/或RPC调用完成的事件。 包括列DatabaseName ， ParentName ， ObjectName ， ServerName ， SPID和RequestID （对于支持MARS的连接）。 也许有些人太。 “审核架构对象访问事件”发生任何时候一个对象被访问，因此锻炼时应用该跟踪正在运行，然后使用SPID +的requestId整理数据，并使用sys.sql_expression_dependencies把它比作你的结果。 如果有什么是不会出现在你的依赖数据的跟踪数据，那么你已经错过了一些东西。
如果你要处理的链接服务器，我适应@ MilicaMedic的回答，为跨服务器的依赖关系的工作。 我也是其中一个依赖可用的输出列名。
您可以使用它像这样：
create table #dependencies (
referencing_server nvarchar(128),
referencing_database nvarchar(128),
referencing_schema nvarchar(128),
referencing_object_name nvarchar(128),
referencing_column nvarchar(128),
referenced_server nvarchar(128),
referenced_database nvarchar(128),
referenced_schema nvarchar(128),
referenced_object_name nvarchar(128),
referenced_column nvarchar(128)
);
insert @dependencies
exec crossServerDependencies
'ThisServerName, LinkedServerName, LinkedServerName2, etc'
从那里，你，你在你的答案说明它加入到你的AllObjects表。
我的代码需要两个外部函数：“splitString”和“AddBracketsWhenNecessary”。 您可以简化前者完全消除后，你的愿望。 但是我将它们用于其他东西，所以他们使它成为我的实现。 两个代码是在底部。
以下是主要步骤：
create procedure crossServerDependencies
@server_names_csv nvarchar(500) = null -- csv list of server names you want to pull dependencies for
as
-- Create output table
if object_id('tempdb..#dependencies') is not null
drop table #dependencies;
create table #dependencies (
referencing_server nvarchar(128),
referencing_database nvarchar(128),
referencing_schema nvarchar(128),
referencing_object_name nvarchar(128),
referencing_column nvarchar(128),
referenced_server nvarchar(128),
referenced_database nvarchar(128),
referenced_schema nvarchar(128),
referenced_object_name nvarchar(128),
referenced_column nvarchar(128)
);
-- Split server csv into table
set @server_names_csv = isnull(@server_names_csv, @@servername);
declare @server_names table (
server_row int,
server_name nvarchar(128),
actuallyExists bit
);
insert @server_names
select server_row = id,
server_name,
actuallyExists = case when sv.name is not null then 1 else 0 end
from dbo.splitString(@server_names_csv, ',') sp
cross apply (select server_name = dbo.AddBracketsWhenNecessary(val)) ap
left join sys.servers sv on sp.val = dbo.AddBracketsWhenNecessary(sv.name);
-- Loop servers
declare
@server_row int = 0,
@server_name nvarchar(50),
@server_exists bit = 0,
@server_is_local bit = 0,
@server_had_some_inserts bit = 0;
while @server_row <= (select max(server_row) from @server_names)
begin
-- Server loop initializations
set @server_row += 1;
set @server_had_some_inserts = 0;
select @server_name = server_name,
@server_exists = actuallyExists
from @server_names
where server_row = @server_row;
set @server_is_local =
case when @server_name = dbo.AddBracketsWhenNecessary(@@servername) then 1 else 0 end;
-- Handle non-existent server (and prevent sql injection)
if @server_exists = 0
begin
print
'"' + @server_name + '" does not exist. ' +
'Please check your spelling and/or access to view the linked server ' +
'(running under ' + user_name() + ').';
continue;
end
-- Get database list
if object_id('tempdb..#databases') is not null
drop table #databases;
create table #databases (
rownum int identity(1,1),
database_id int,
database_name nvarchar(128)
);
declare @sql nvarchar(max) = '
select database_id, [name]
from master.sys.databases
where state <> 6 -- ignore offline dbs
and database_id > 4 -- ignore system dbs
and has_dbaccess([name]) = 1
and [name] not in (''ReportServer'', ''ReportServerTempDB'')
';
if @server_is_local = 0
begin
set @sql = replace(@sql, '''', '''''');
set @sql = 'select * from openquery( @server_name, ''' + @sql + ''')';
end
set @sql = 'insert #databases (database_id, database_name)' + @sql;
set @sql = replace(@sql, '@server_name', @server_name);
exec (@sql);
delete #databases
where database_name = 'ReportServer';
-- Loop databases
declare @rowNum int = 0;
while @rowNum <= (select max(rownum) from #databases)
begin
-- Database loop initializations
set @rowNum += 1;
declare
@database_id nvarchar(max),
@database_name nvarchar(max);
select @database_id = database_id,
@database_name = dbo.AddBracketsWhenNecessary(database_name)
from #databases
where rownum = @rowNum;
-- Get object dependency info
set @sql = '
with
getTableColumnIds as (
select table_id = o.object_id,
table_name = o.name,
column_id = c.column_id,
column_name = c.name
from @database_name.sys.objects o
join @database_name.sys.all_columns c on o.object_id = c.object_id
)
@insertStatement
select ''@server_name'',
db_name(@database_id),
object_schema_name(referencing_id, @database_id),
object_name(referencing_id, @database_id),
referencing_column = ringTCs.column_name,
isnull(referenced_server_name, ''@server_name''),
isnull(referenced_database_name, db_name(@database_id)),
isnull(referenced_schema_name, ''dbo''),
referenced_entity_name,
referenced_column = redTCs.column_name
from @database_name.sys.sql_expression_dependencies d
left join getTableColumnIds ringTCs
on d.referencing_id = ringTCs.table_id
and d.referencing_minor_id = ringTCs.column_id
left join getTableColumnIds redTCs
on d.referenced_id = redTCs.table_id
and d.referenced_minor_id = redTCs.column_id
';
set @sql = replace(@sql, '@database_id', @database_id);
set @sql = replace(@sql, '@database_name', @database_name);
if @server_is_local = 0
begin
set @sql = replace(@sql, '''', '''''');
set @sql = replace(@sql, '@insertStatement', '');
set @sql = 'select * from openquery(@server_name, ''' + @sql + ''')';
end
set @sql = replace(@sql, '@insertStatement', 'insert #dependencies ');
set @sql = replace(@sql, '@server_name', @server_name);
exec (@sql);
-- Database loop terminations
if @@rowcount > 0
set @server_had_some_inserts = 1;
end -- database loop
-- server loop terminations
if @server_had_some_inserts = 0
begin
declare @remote_user_name nvarchar(255);
select @remote_user_name = remote_name
from sys.linked_logins li
join sys.servers s on li.server_id = s.server_id
where remote_name is not null
and s.name = 'sisag'
print (
'No dependencies found for ' + @server_name + '. ' +
'If this is unexpected, you may need to run "grant view any definition to ' +
'[' + isnull(@remote_user_name, '?') + ']" ' +
'on the remote server.'
);
end
end -- server loop
-- Terminate
select * from #dependencies
对于AddBracketsWhenNecessary代码：
create function AddBracketsWhenNecessary (
@objectName nvarchar(250)
)
returns nvarchar(250) as
begin
if left(@objectName, 1) = '[' and right(@objectName, 1) = ']'
return @objectName;
declare @hasInvalidCharacter bit;
select @hasInvalidCharacter = max(isInvalid)
from dbo.splitString(@objectName, null) chars
cross apply (select
isLetter = patindex('[a-z,_]', val),
isNumber = PATINDEX('[0-9]', val)
) getCharType
cross apply (select
isInvalid =
case
when isLetter = 1 then 0
when isNumber = 1 and not chars.id = 1 then 0
else 1
end
) getValidity
return
case when @hasInvalidCharacter = 1 then '[' else '' end
+ @objectName
+ case when @hasInvalidCharacter = 1 then ']' else '' end;
end
任何终于，我的分路器功能（但见阿诺Fribble 在这里 ，如果你想有一个简单的版本，或者使用内置的功能，如果你有SqlServer的2016或以上）：
create function splitString (
@stringToSplit nvarchar(max),
@delimiter nvarchar(50)
)
returns table as
return
with
split_by_delimiter as (
select id = 1,
start = 1,
stop = convert(int,
charindex(@delimiter, @stringToSplit)
)
union all
select id = id + 1,
start = newStart,
stop = convert(int,
charindex(@delimiter, @stringToSplit, newStart)
)
from split_by_delimiter
cross apply (select newStart = stop + len(@delimiter)) ap
where Stop > 0
),
split_into_characters as (
select id = 1,
chr = left(@stringToSplit,1)
union all
select id = id + 1,
chr = substring(@stringToSplit, ID + 1, 1)
from split_into_characters
where id < len(@stringToSplit)
)
select id,
val =
ltrim(rtrim(substring(
@stringToSplit,
start,
case
when stop > 0 then stop - start
else len(@stringtosplit)
end
)))
from split_by_delimiter
where len(@delimiter) > 0
union all
select id,
val = chr
from split_into_characters
where @delimiter = ''
or @delimiter is null
我不得不做出从我用的是真正的代码，一些小的修改，因此，如果有任何引用错误，请让我知道在评论，我会编辑。
