Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 как определить в каких хранимках используется поле из таблицы  [new]
netivan
Member

Откуда:
Сообщений: 8768
Есть такая задача. Есть таблица Т и в ней есть поле ID. Вопрос - как определить в каких процедурах оно используется?
Мои варианты:
1. смотреть по описанию не вариант - очень много может быть комбинаций.
2.более надежный - переименовать поле и сделать Всем процедурам ALTER Proc ..
Но во 2ом варианте вопрос - как мне сгенерить ALTER? Менять системный текст create на alter тоже нетривиальная задача.
Вообщем хочу полезный совет :)
ЗЫ пришла мысль как-то вызвать для каждой функции окно "мастера" Generate alter script, но пока не нашел как.
8 окт 12, 16:32    [13285779]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
komrad
Member

Откуда:
Сообщений: 5252
netivan
ЗЫ пришла мысль как-то вызвать для каждой функции окно "мастера" Generate alter script, но пока не нашел как.


проверяйте на имя таблицы - может использоваться * в запросах
8 окт 12, 16:34    [13285794]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
netivan
Member

Откуда:
Сообщений: 8768
komrad,

ну таблица используется часто, поэтому считаем, что * - будет погрешность :)
8 окт 12, 16:37    [13285822]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104760
netivan
Всем процедурам ALTER Proc ..
Но во 2ом варианте вопрос - как мне сгенерить ALTER? Менять системный текст create на alter тоже нетривиальная задача.

В смысле ?

use master
go
print STUFF(OBJECT_DEFINITION(OBJECT_ID('sp_who')),1,6,'alter')
8 окт 12, 16:50    [13285901]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
komrad
Member

Откуда:
Сообщений: 5252
netivan
komrad,

ну таблица используется часто, поэтому считаем, что * - будет погрешность :)


sp_depends и рекомендованные вместо него dmv
8 окт 12, 16:55    [13285935]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Glory
netivan
Всем процедурам ALTER Proc ..
Но во 2ом варианте вопрос - как мне сгенерить ALTER? Менять системный текст create на alter тоже нетривиальная задача.

В смысле ?

use master
go
print STUFF(OBJECT_DEFINITION(OBJECT_ID('sp_who')),1,6,'alter')
А перед CREATE может быть столько всего!
А если там 100500 раз CREATE PROC встречается?
8 окт 12, 16:57    [13285961]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Glory
В смысле ?

use master
go
print STUFF(OBJECT_DEFINITION(OBJECT_ID('sp_who')),1,6,'alter')

use model;
go
-- Превед
create procedure qweqwe100500
as
  set nocount on;
go

print STUFF(OBJECT_DEFINITION(OBJECT_ID('qweqwe100500')),1,6,'alter')
go
drop procedure qweqwe100500;
go
8 окт 12, 16:58    [13285964]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
iap
А перед CREATE может быть столько всего!
А если там 100500 раз CREATE PROC встречается?
В комментариях, я имею в виду
8 окт 12, 16:58    [13285966]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104760
iap
А перед CREATE может быть столько всего!
А если там 100500 раз CREATE PROC встречается?

Нужно одно единственное. Оно же - первое.
8 окт 12, 17:01    [13285985]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
Зайцев Фёдор
Member

Откуда: Лужки
Сообщений: 5308
Glory
Оно же - первое.

из чего это следует?
8 окт 12, 17:03    [13285997]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
netivan
Member

Откуда:
Сообщений: 8768
Glory,

неверно. Там вариантов может быть много. я Уже написал "самый первый" create.
И еще может быть варианты "create proc","create proc" ,"create proc" .. и тд. Менять первый create тоже стремно, т.к. в комментах есть create date =). Вообще подмена гиблое дело, надо понять как вызвать мастер.
8 окт 12, 17:11    [13286075]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
netivan
Glory,

неверно. Там вариантов может быть много. я Уже написал "самый первый" create.
И еще может быть варианты "create proc","create proc" ,"create proc" .. и тд. Менять первый create тоже стремно, т.к. в комментах есть create date =). Вообще подмена гиблое дело, надо понять как вызвать мастер.
Ну, не гиблое, конечно.
Но писать свой парсер литеральных строк + блочных и строковых комментариев придётся.
8 окт 12, 17:15    [13286098]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Если позволит версия сервера:
select
 o.name 
from
 sys.objects o cross apply
 sys.dm_sql_referenced_entities(quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name), 'object') re
where
 o.type = 'P' and
 re.referenced_id = object_id('T', 'U') and
 re.referenced_minor_name = 'ID';
Заодно выясните какие процедуры ссылаются на несуществующие объекты.
8 окт 12, 17:28    [13286178]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
Гузы
Guest
netivan,
Запускаешь профайлер, правой - скрипт на изменение, получишь 1500 строк кода создания скрипта изменить процедуру или еще там чего... А дальше - полет фантазии.
8 окт 12, 17:41    [13286276]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
netivan
Member

Откуда:
Сообщений: 8768
invm,

позволяет. Сейчас разберусь с запросом, 2 ошибки выдал :)
8 окт 12, 18:23    [13286593]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
kain111
Member

Откуда:
Сообщений: 226
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



-- =============================================
-- Author:    Antonin Foller, www.foller.cz
-- Create date: 2007-09-19
-- Description: Search a text in stored procedure source code.
-- @text - any text to find, search is done by like '%text%'
-- @dbname - database where to search, 
--         - if omitted, all databases in the SQL server instance

--exec find_text_in_sp mt_Sales_Plan_S
-- =============================================
ALTER PROCEDURE [dbo].[find_text_in_sp]
  @text varchar(250),
  @dbname varchar(64) = null
AS BEGIN
SET NOCOUNT ON;

if @dbname is null
  begin
    --enumerate all databases.
  DECLARE #db CURSOR FOR Select Name from master..sysdatabases
  declare @c_dbname varchar(64)

  OPEN #db FETCH #db INTO @c_dbname
  while @@FETCH_STATUS <> -1 --and @MyCount < 500
   begin
     execute find_text_in_sp @text, @c_dbname
     FETCH #db INTO @c_dbname
   end  
  CLOSE #db DEALLOCATE #db
 end --if @dbname is null
else
 begin --@dbname is not null
  declare @sql varchar(250)
  --create the find like command
  select @sql = 'select ''' + @dbname + ''' as db, o.name,m.definition '
  select @sql = @sql + ' from '+@dbname+'.sys.sql_modules m '
  select @sql = @sql + ' inner join '+@dbname+'..sysobjects o on m.object_id=o.id'
  select @sql = @sql + ' where [definition] like ''%'+@text+'%'''
  execute (@sql)
--  SELECT @sql
 end --@dbname is not null
END

скомуниздил у кого то.
9 окт 12, 08:07    [13288104]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
netivan
Мои варианты:
1. смотреть по описанию не вариант - очень много может быть комбинаций.
2.более надежный - переименовать поле и сделать Всем процедурам ALTER Proc ..
Ещё варианты:
3. Сгенерить скрипт создания базы с объектами, развернуть на другой базе, переименовать поле, запустить скрипт сохздания процедур.
4. Сгенерить скрипт создания процедур с удалением (там опция есть), переименовать поле, запустить скрипт
5. Импортить модель базы в проект Visual Studio, переименовать поле, посмотреть в списке ошибок проекта ошибки.
9 окт 12, 08:30    [13288132]     Ответить | Цитировать Сообщить модератору
 Re: как определить в каких хранимках используется поле из таблицы  [new]
netivan
Member

Откуда:
Сообщений: 8768
invm,

спасибо, немного переписал вот что вышло:
--*********************************----
---------поиск зависимостей в процедурах----------
--*********************************----
DECLARE @searchtablename VARCHAR(MAX)='имя таблицы без схемы',@searchcolumnname VARCHAR(MAX)='имя столбца'
DECLARE @name VARCHAR(MAX),@shname VARCHAR(MAX)
DECLARE @reftable TABLE(procname VARCHAR(MAX),name VARCHAR(MAX),colname VARCHAR(MAX)) -- вот тут результаты
DECLARE @errtable TABLE(errname VARCHAR(MAX),errmsg VARCHAR(MAX)) -- ошибки: ненайденные сущности
----
DECLARE SPList CURSOR FOR 
SELECT  o.name,QUOTENAME(SCHEMA_NAME(o.schema_id))
	FROM sys.objects o
	WHERE TYPE = 'P'
    OPEN SPList    
    FETCH NEXT FROM SPList    
    INTO @Name,@shname 
    WHILE @@FETCH_STATUS = 0
    BEGIN
		BEGIN TRY
		INSERT @reftable
		SELECT
		@name, referenced_entity_name AS table_name, referenced_minor_name AS column_name
		FROM
		 sys.dm_sql_referenced_entities(@shname + N'.' + @name, 'object') re
		WHERE
		 --re.referenced_id = object_id('T', 'U') and
		 re.referenced_minor_name = @searchcolumnname AND
		 re.referenced_entity_name=@searchtablename
		 END TRY
		 BEGIN CATCH
		 INSERT @errtable
		SELECT
					@name AS 'ProcName',
					ERROR_MESSAGE() AS ErrorMessage
		 END CATCH
	FETCH NEXT FROM SPList
	INTO @Name,@shname
	END
	
		
CLOSE SPList
DEALLOCATE SPList
--смотрим результат
SELECT * FROM @reftable ORDER BY procname
SELECT * FROM @errtable
--*********************************----
---------поиск внешних ключей----------
--*********************************----
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE COL_NAME(fc.referenced_object_id,fc.referenced_column_id)=@searchcolumnname AND
	  OBJECT_NAME (f.referenced_object_id)=@searchtablename
9 окт 12, 13:05    [13289840]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить