Finding Differing Module Definitions

• BurikkuDeibu

  SSC Eights!

  Points: 812

  More actions

  May 29, 2013 at 7:22 am

  #294529

  So, I've been given the task of improving the performance of a database that was acquired via the purchase of a different company. It's not a pretty situation, but I'm slowly taking baby steps to improve performance.

  One of the first things I'm doing is making sure all the stored procedures start with SET NOCOUNT ON. So, I've gone through the scripts for a selected subset of the stored procedures and made sure they start with that statement. That's where my problems start...

  Each stored procedure is only under source control once. However, there are hundreds of instances of it; one for each instance of a customer database. And, of course, there are a few cases where the previous developers made modifications to a few specific customer's databases and didn't add those changes into source control. So, I'm afraid to just run the scripts I've got, as I might wipe out some customer specific changes.

  The first thing I'm doing is trying to identify all the different definitions for a stored procedure. Here's what I've got so far, and it works alright. But I thought I'd throw this out to the community and see if anybody has any comments or suggestions...

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  SET NOCOUNT ON;

  -- To look for a different module, change @ModuleName to be the schema qualified

  -- name of the module you want to check.

  DECLARE @ModuleName SYSNAME = N'[lookup].[RetrieveColor]';

  -- Get a list of all customer databases.

  DECLARE @DatabaseList TABLE

  (

  IdINTIDENTITY(1,1)

  , DBNameSYSNAME

  );

  INSERT INTO @DatabaseList

  (

  DBName

  )

  SELECT

  [name]

  FROM [sys].[databases]

  WHERE ([name] <> N'master')

  AND ([name] <> N'model')

  AND ([name] <> N'msdb')

  AND ([name] <> N'tempdb')

  AND ([name] <> N'ReportServer')

  AND ([name] <> N'ReportServerTempDB')

  AND ([name] <> N'LiteSpeedLocal')

  AND ([name] <> N'SQLdmRepository')

  AND ([name] <> N'testdb')

  AND ([name] <> N'BillingSystemController')

  AND ([name] NOT LIKE N'xdoc%')

  AND ([name] NOT LIKE N'%demo%')

  DECLARE @DBIndex INT = 1;

  DECLARE @SqlCOmmand NVARCHAR(Max);

  DECLARE @ModuleDefs TABLE

  (

  DefIdINTIDENTITY(1,1)

  , IdINT

  , DBNameSYSNAME

  , DefNVARCHAR(Max)

  )

  -- From each customer database, retrieve the definition of the module in question.

  -- As include a "Not Defined" row, in case the module doesn't exist. We'll deal with

  -- quasi-duplicates later.

  WHILE @DBINdex <= (SELECT Max(Id)

  FROM @DatabaseList )

  BEGIN

  SELECT

  @SqlCommand = N'SELECT ' +

  CAST(D.Id AS NVARCHAR) +

  N' AS [Id], ''' +

  D.DBName +

  N''' AS [DBName], ' +

  N'M.definition FROM [' +

  D.DBName +

  N'].[sys].[sql_modules] M INNER JOIN [' +

  D.DBName +

  N'].[sys].[objects] O ON M.[object_id] = O.[object_id] WHERE Object_Id(''[' +

  D.DBName +

  N'].' +

  @ModuleName +

  N''') = O.[object_id] UNION SELECT ' +

  CAST(D.Id AS NVARCHAR) +

  N' AS [Id], ''' +

  D.DBName +

  N''' AS [DBName], ''Not Defined'' ORDER BY definition DESC'

  FROM @DatabaseList D

  WHERE D.[ID] = @DBIndex;

  PRINT @SqlCommand;

  INSERT @ModuleDefs

  EXEC sp_executeSQL @SqlCommand;

  SET @DBIndex += 1;

  END;

  -- Rank the definitions partitioned by database, and just take the top definition.

  SELECT

  Rank() OVER(PARTITION BY Id ORDER BY DefId) AS DBOrder

  , DBName

  , Def

  FROM @ModuleDefs;

  WITH RankedDefs AS

  (

  SELECT

  Rank() OVER(PARTITION BY Id ORDER BY DefId) AS DBOrder

  , DBName

  , Def

  FROM @ModuleDefs

  )

  SELECT

  RD1.DBName

  , RD1.Def

  , RD2.DBName

  , RD2.Def

  FROM RankedDefs RD1

  CROSS JOIN RankedDefs RD2

  WHERE RD1.DBOrder = 1

  AND RD1.DBName <> RD2.DBName

  AND RD1.Def <> RD2.Def

  One thing I'm seeing, which I can't explain is... If I change 'Not Defined' in the dynamic SQL to be the empty string or NULL, then later on in the CROSS JOIN I get two rows for each database where the definition differs; one for RD1 and one for RD2. But, if I use anything else, I get just one row. I can't figure this one out. Either way, I know how to read the results. But, it sure would be nice to know what's going on.

  If anybody has ideas on a more eloquent way to accomplish my goal, or an explanation for the weirdness I mentioned, I'd love to hear it.

  Or, if you just want to give me a little sympathy for the messiness I need to deal with, that'd be appreciated, too. 😀

  Thanks,
  MKE Data Guy