Я пытаюсь найти все ссылки на объект в базе данных SQL Server.
Как я могу быстро выполнить поиск? SQL Server Management Studio, похоже, не делает этого. Я использую http://www.red-gate.com/products/SQL_Search/, но я бы хотел найти "официальное" решение Microsoft для этого. Это в другом продукте?
Например, когда я делаю массовый поиск в visual studio, я хотел бы также найти что-то во всех хранимых процедурах.
Или, может быть, я не правильно это кодирую?
Карл
Использование:
select object_name(m.object_id), m.*
from sys.sql_modules m
where m.definition like N'%name_of_object%'
... потому что SYSCOMMENTS и INFORMATION_SCHEMA.routines имеют столбцы nvarchar (4000). Поэтому, если "name_of_object" используется в позиции 3998, он не будет найден. SYSCOMMENTS имеет несколько строк, но INFORMATION_SCHEMA.routines усекает.
С использованием недокументированного SQL sp: sp_msforeachdb
exec sp_msforeachdb '
USE [?];
--IF DB_NAME() NOT IN (''master'',''tempdb'',''model'',''msdb'')
BEGIN
DECLARE
@SearchStr varchar(100)
SET @SearchStr = ''%column_store_segments%'';
SELECT DISTINCT
''?'' as db_name, o.name
, ( CASE upper(o.xtype)
WHEN ''C'' THEN ''CHECK constraint''
WHEN ''D'' THEN ''Default or DEFAULT constraint''
WHEN ''F'' THEN ''FOREIGN KEY constraint''
WHEN ''L'' THEN ''Log''
WHEN ''FN'' THEN ''Scalar function''
WHEN ''IF'' THEN ''Inline table-function''
WHEN ''PK'' THEN ''PRIMARY KEY or UNIQUE constraint''
WHEN ''P'' THEN ''Stored procedure''
WHEN ''R'' THEN ''Rule''
WHEN ''RF'' THEN ''Replication filter stored procedure''
WHEN ''S'' THEN ''System table''
WHEN ''TF'' THEN ''Table function''
WHEN ''TR'' THEN ''Trigger''
WHEN ''U'' THEN ''User table''
WHEN ''V'' THEN ''View''
WHEN ''UQ'' THEN ''UNIQUE constraint (type is K)''
WHEN ''X'' THEN ''Extended stored procedure''
ELSE upper(o.xtype) END ) Type
, ( CASE upper(o.xtype)
WHEN ''PK'' THEN ( select object_name(parent_object_id) FROM sys.key_constraints (nolock) WHERE o.name=name )
WHEN ''F'' THEN ( select object_name(parent_object_id) FROM sys.foreign_keys (nolock) WHERE o.name=name )
WHEN ''TR'' THEN ( select object_name(parent_id) FROM sys.triggers (nolock) WHERE o.name=name )
ELSE '''' END ) as Parent_Object
FROM sysobjects o (nolock)
INNER JOIN syscomments sc (nolock) ON o.id = sc.id
WHERE UPPER( text ) LIKE UPPER( @SearchStr ) AND substring(o.name,1,3)<> ''dt_''
GROUP BY o.name, o.xtype
END'
GO
Я использую этот запрос для поиска всех таблиц (или текста) в хранимых процедурах:
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
Если вы хотите использовать OMG Ponies sql в качестве сочетания клавиш в SSMS, добавьте следующий SP к своему мастеру db.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FindAllReferences]
@targetText nvarchar(128)
AS
BEGIN
SET NOCOUNT ON;
declare @origdb nvarchar(128)
select @origdb = db_name()
declare @sql nvarchar(1000)
set @sql = 'USE [' + @origdb +'];'
set @sql += 'select object_name(m.object_id), m.* '
set @sql += 'from sys.sql_modules m where m.definition like N' + CHAR(39) + '%' + @targetText + '%' + CHAR(39)
exec (@sql)
SET NOCOUNT OFF;
END
Затем вам просто нужно добавить dbo.SP_FindAllReferences к вашим ярлыкам на клавиатуре, а затем вы можете использовать его в контексте любой БД на вашем сервере.
Ура!
Примечание. Если вы используете SQL Server 2005, вам придется заменить
@sql +=
с
@sql = @sql +
Я нашел такое решение.
USE [Database]
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
referenced_entity_name = 'SP_Pay_GetData'
order by referencing_object_name
Я не уверен в "официальном Microsoft", но я использовал SqlDigger в прошлом. Это не плохо.
Если вы хотите сделать это в VS, вам понадобится текст всех ваших процессов, включенных в ваш проект.
SQL Server Management Studio имеет функцию View Dependencies при щелчке правой кнопкой мыши по объекту в обозревателе объектов. Это то, что вы ищете?
В SQL 2008 был добавлен DMV (функция управления данными) sys.dm_sql_referencing_entities. Он возвращает любой объект, который ссылается на объект, который вы передаете ему.
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Table1', 'OBJECT')
Очень поздно на вечеринку, но...
Вы можете использовать системный sys.sp_depends:
exec sys.sp_depends 'object_name'
Результатом является таблица, в которой перечислены все объекты базы данных, которые зависят (то есть, от ссылки) object_name. Каждая строка содержит имя и тип ссылающегося объекта, а также другие информационные столбцы, в зависимости от типа object_name.
Примечание. Этот процесс был добавлен в MS SQL Server 2008.
Смотрите: MSDN документы
В документах говорится, что этот процесс может быть удален в будущем выпуске, и вместо него будет использоваться sys.dm_sql_referencing_entities, но он все еще жив и работает в MS SQL 2017.
В SQL Server 2000 здесь есть запрос, который может искать в определениях объектов, поддерживая строки поиска до 2000 символов. Он использует куски в таблице syscomments.
SELECT O.name, O.xtype
FROM sysobjects O
WHERE EXISTS (
SELECT *
FROM
(
SELECT
Chunk = Substring(C1.text, T.Offset, 4000)
+ Coalesce(Substring(C2.text, 1, T.AdditionalLength), '')
FROM
syscomments C1
CROSS JOIN (
SELECT 1, 0
UNION ALL
SELECT 2001, 2000
) T (Offset, AdditionalLength)
LEFT JOIN syscomments C2
ON C1.id = C2.id
AND C1.colid + 1 = C2.colid
AND T.Offset > 1
WHERE
O.id = C1.id
) C
WHERE
Chunk LIKE '%search string%'
);