Wednesday, 10 May 2017

Searching SQL Code via sys.sql_modules

I've been used to searching stored procedures via tsql like this.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%mytablename%'

The problem this encounteres however is that ROUTINE_DEFINTION is NVARCHAR(4000) and hence only returns the first 4000 characters of a stored procedure. The alternative below searches the entire codebase.

SELECT DISTINCT
   SCHEMA_NAME(schema_id) as schema_name
  ,o.name AS Object_Name
  ,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%mytablename%'
ORDER BY 1,2

Thursday, 4 May 2017

Converting a datestring to datetime (yyyymmddhhmmss)

This uses STUFF to format a datestring correctly.

declare @datestring varchar(20) = '20170504103253'
-- Add colons and space to format datetime
SET @datestring = STUFF(STUFF(STUFF(@datestring,13,0,':'),11,0,':'),9,0,' ')
select @datestring
-- Convert ....
select convert(datetime,@datestring)

2017-05-04 10:32:53

2017-05-04 10:32:53.000

Saturday, 11 March 2017

COUNT without GROUP BY

I found a novel way of doing a record count today, without using a GROUP BY.
Normally I would have joined another results set containing a GROUP BY, but it appears you can use the OVER window function without a PARTITION BY / ORDER BY statement.

The syntax is COUNT(*) OVER () AS [RecordCount]

I use it below to return the count of addresses in AdventureWorks.

SELECT DISTINCT 
      a.[City]
   ,sp.Name As StateProvince
   ,cr.Name AS CountryRegion
   ,COUNT(*) OVER (PARTITION BY sp.[StateProvinceID]) AS AddressesInThisProvince
   ,COUNT(*) OVER (PARTITION BY cr.CountryRegionCode) AS AddressesInThisCountry
   ,COUNT(*) OVER () AS TotalAddresses 
  FROM [AdventureWorks2014].[Person].[Address] a
  INNER JOIN [AdventureWorks2014].[Person].[StateProvince] sp
  ON a.[StateProvinceID] = sp.[StateProvinceID]
  INNER JOIN [AdventureWorks2014].[Person].[CountryRegion] cr
  ON sp.CountryRegionCode = cr.CountryRegionCode
  WHERE cr.Name = 'Germany'
  ORDER BY 1,2,3