Toolbox: Hitta alla objekt i SQL Server 2000

Har du någon gång suttit med den ärofyllda uppgiften att uppdatera alla lagrade procedurer som innehåller en viss tabell till att referera en annan? Detta kan vara extremt arbetssamt om det är en äldre databas som har många lagrade procedurer och där namnkonventioner inte anammats konsekvent. Som jag ser det finns det två alternativ att åstadkomma detta arbete:

1. Gå igenom alla lagrade procedurer och hitta de referenser som ska bytas ut.
2. Sök fram alla lagrade procedurer som innehåller tabellnamnet och uppdatera dessa.

Den första punkten är ganska rakt på, bara att öppna procedurerna i någon form, förhoppningsvis strukturerat... :-)

Den andra är nästan lika rakt. All information om objekt i databasen finns lagrat i olika systemtabeller osv. Tyvärr är det inte många som vågat sig på att titta i dessa för att se vilken information som finns där. Den lagrade proceduren nedan gör just vad vi vill åstadkomma i punkt två. Sök igenom den information som finns i databasen om de olika objekten och ta fram de objekt som matchar baserat på en viss sträng, exempelvis ett tabellnamn. Denna procedur tar inte bara fram de lagrade procedurer som matchar utan även vyer, tabeller, jobb osv. Du ser själv i T-SQLkoden vilka.

CREATE PROCEDURE find_text
@Search VARCHAR(200)

--  Search the stored procedures and Scheduled Jobs for the text string passed
--  and show which objects it is found.

AS

SET NOCOUNT ON

SET @Search = '%' + @Search + '%'

-- Create a table var to store each part
DECLARE @tmpSearch TABLE  ([Object Name] VARCHAR(50),[Sub Level]
VARCHAR(40),[Object Type] VARCHAR(24))

INSERT INTO @tmpSearch
   SELECT DISTINCT a.Name,SPACE(40) AS 'Sub Level',
                CASE WHEN a.xType = 'C' THEN 'Check Constraint'
                    WHEN a.xType = 'D' THEN 'Default constraint'
                    WHEN a.xType = 'F' THEN 'FK constraint'
                    WHEN a.xType = 'L' THEN 'Log'
                    WHEN a.xType = 'FN' THEN 'Scaler Function'
                    WHEN a.xType = 'IF' THEN 'Inline Function'
                    WHEN a.xType = 'P' THEN 'Stored Procedure'
                    WHEN a.xType = 'PK' THEN 'PK constraint'
                    WHEN a.xType = 'RF' THEN 'Replication Filer'
                    WHEN a.xType = 'S' THEN 'System Table'
                    WHEN a.xType = 'TF' THEN 'Table Function'
                    WHEN a.xType = 'TR' THEN 'Trigger'
                    WHEN a.xType = 'U' THEN 'User Table'
                    WHEN a.xType = 'UQ' THEN 'Unique constraint'
                    WHEN a.xType = 'V' THEN 'View'
                    WHEN a.xType = 'X' THEN 'Extended SP'
                    ELSE 'Other' END AS Object_Type
   FROM sysObjects AS a WITH (NOLOCK)
   INNER JOIN syscomments AS b WITH (NOLOCK) ON a.ID = b.ID
   WHERE ENCRYPTED = 0
   AND Text LIKE @Search

INSERT INTO @tmpSearch
   SELECT A.[Name],B.Step_Name,'Scheduled Job' AS Object_Type
   FROM MSDB..SYSJOBS AS A WITH (NOLOCK)
   INNER JOIN MSDB..SYSJOBSTEPS AS B WITH (NOLOCK)
   ON A.JOB_ID = B.JOB_ID
   WHERE B.Command LIKE @Search

INSERT INTO @tmpSearch
   SELECT t.table_name, ' ', 'User Table' FROM INFORMATION_SCHEMA.tables t
   INNER JOIN INFORMATION_SCHEMA.columns c
   ON c.table_name = t.table_name
   WHERE column_name like  @Search

SELECT [Object Name],[Sub Level],[Object Type]
FROM @tmpSearch
ORDER BY [Object Type],[Object Name]

Kör detta script i den databas som är intressant och anropa den sedan med en sträng för att söka fram en lista med objekt. För att söka efter alla objekt som innehåller strängen "Adress" så skriver du följande:

find_text 'Adress'

Detta script är avsett för SQL Server 2000 men fungerar även i SQL Server 2005. I SQL Server 2005 bör man dock byta ut systemtabellerna mot de vyer som är motsvarande. Jag har inte själv skrivit denna kod, vet du vem som gjorde originalet, låt mig veta så ska denne uppmärksammas.