Monthly Archives: December 2008

SQL Server Schema,Table,Function and Stored procedure Auditing

By | Code, iPhone, MSSQL2005, MSSQL2008, Objective-C, Software Development, SQL Server, TSQL | One Comment

If you like to know what’s changing on your SQL Server then the following code creates an audit table and the associated trigger for logging the changes

The Audit Object Table

CREATE TABLE [dbo].[AuditObjects](
 [EventID] [INT] IDENTITY(1,1) NOT NULL
,[EventData] [xml] NULL
PRIMARY KEY CLUSTERED (
   [EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And the associated Trigger

CREATE TRIGGER [TriggerAuditObjects]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO [dbo].[AuditObjects](EventData)
SELECT EVENTDATA()
GO
ENABLE TRIGGER [TriggerAuditObjects] ON DATABASE

You can now use xpath queries to analyse the data

Search Stored Procedures

By | Code, Database, iPhone, MSSQL2005, MSSQL2008, Objective-C, Quick Tips, SQL Server, TSQL | No Comments

Ever had the need to search stored procedures to find redundant tables or text that needs replacing or re-factoring ?

Here’s a script to help (Sorry for the cursor, I’m sad with myself for using it 🙁 )

-- Search string
DECLARE @SearchString nvarchar(50)
SET @SearchString = 'Search String'
 
-- Required Declares
DECLARE @getdbname sysname
DECLARE @sqlstm nvarchar(1000)
DECLARE SeachCursor cursor FOR 
-- Get All The Names into the SeachCursor
SELECT '['+name+']' FROM [master].[dbo].[sysdatabases] ORDER BY name 
OPEN SeachCursor
-- Add the Search Pattern
SET @SearchString = '%' + @SearchString + '%'
--Get the first Name
FETCH NEXT FROM SeachCursor INTO @getdbname
WHILE @@FETCH_STATUS=0
	BEGIN
	--set the statement to define the search condition, with variables
	SET @sqlstm = '
	SELECT [SP].[Specific_Catalog] AS [Database_Name], [SP].[Routine_Name] AS [Stored Procedure Name],[SP].[Routine_Definition] AS [Routine_Definition]
	FROM '+ @getdbname+'.[Information_Schema].[Routines] AS [SP]
	WHERE PatIndex('+''''+@SearchString+''''+', [Routine_Definition]) > 0'
	--Execute the Query
	EXEC (@sqlstm)
	FETCH NEXT FROM SeachCursor INTO @getdbname
	END
--Close the Cursor and Deallocate it from memory
CLOSE SeachCursor
DEALLOCATE SeachCursor