Sometime times I get a request for data from a user that is actually fun to write.  Today was one like that.  The request from the user was to look at a series of event that occurred in the last week.  It needed to be organized by day of week and time of day, by the half hour.  Now that it is done I see a series of user reports in my future that takes this data and presents it for user review.

Here is what the data looks like after it is run:

Num Files	Hour Of Day	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
23	6:00	6	2	4	7	2	1	1
80	6:30	12	17	19	17	10	5	0
616	7:00	86	132	129	137	115	17	0
1006	7:30	130	214	183	239	177	63	0
1963	8:00	285	374	449	390	389	76	0
2613	8:30	391	523	557	488	547	107	0
3529	9:00	588	750	690	744	648	109	0
3670	9:30	692	787	701	701	663	126	0
4214	10:00	699	944	851	847	738	133	2
4753	10:30	835	1004	902	955	938	118	1
4850	11:00	885	976	1019	917	954	96	3
4549	11:30	826	920	913	927	883	76	4
4504	12:00	821	866	869	1077	796	72	3
3792	12:30	694	757	793	765	705	75	3
4170	13:00	738	898	838	874	776	42	4
4333	13:30	908	891	868	814	801	49	2
4234	14:00	856	866	774	880	800	54	4
3948	14:30	733	833	787	788	767	36	4
4721	15:00	860	930	1121	890	882	36	2
5089	15:30	1111	1013	977	985	967	30	6
4773	16:00	962	974	937	874	1005	20	1
4753	16:30	944	971	986	865	947	38	2
4516	17:00	958	932	908	855	844	14	5
3519	17:30	716	727	696	687	679	13	1
2761	18:00	497	558	560	600	538	6	2
2129	18:30	447	446	424	432	377	3	0
852	19:00	183	180	184	150	151	4	0
53	19:30	9	8	15	7	8	6	0
24	20:00	9	1	8	0	2	1	3
6	20:30	0	0	0	0	1	0	5
19	21:00	2	4	4	6	2	0	1
4	21:30	1	0	1	0	0	0	2

Here is the SQL used to create it:

SELECT COUNT(Audit_Row_Id) AS NumberFilesRun
     , RIGHT('00' + CAST(DATEPART(HOUR, FI.Audit_Create_Date) AS NVARCHAR(2)), 2) 
       + ':' 
       + (CASE WHEN DATEPART(MINUTE, FI.Audit_Create_Date) < 30 THEN '00' ELSE '30' END) 
       AS HourOfDay
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Monday' THEN 1 ELSE 0 END) AS Monday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Tuesday' THEN 1 ELSE 0 END) AS Tuesday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Wednesday' THEN 1 ELSE 0 END) AS Wednesday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Thursday' THEN 1 ELSE 0 END) AS Thursday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Friday' THEN 1 ELSE 0 END) AS Friday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Saturday' THEN 1 ELSE 0 END) AS Saturday
     , SUM(CASE WHEN  datename(dw,Audit_Create_Date) = 'Sunday' THEN 1 ELSE 0 END) AS Sunday
FROM Audit_File_Information FI
WHERE Audit_Create_User = 'UniqueUserName'
AND Audit_Create_Date BETWEEN DATEADD(week, -2, GETDATE()) AND  DATEADD(week, -1, GETDATE())
GROUP BY RIGHT('00' 
             + CAST(DATEPART(HOUR, FI.Audit_Create_Date) AS NVARCHAR(2)), 2) 
             + ':' 
			 + (CASE WHEN DATEPART(MINUTE, FI.Audit_Create_Date) < 30 THEN '00' ELSE '30' END)
ORDER BY 2 

There are times when I am doing some research or refactoring and I need to find all instances of a value in a database. Things like “remove all instances of a person with id 123” or fix all instances of “The Misspelled Cumpany Name”. To do this I use the following SQL tool that searches all columns in all tables of a database for matching instances of the value passed in.  It takes a while but will find all instances you are looking for.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
BEGIN
 DROP TABLE #Results
END
/**** Customize ***************************************************/
-- The string you are searching for
DECLARE @SearchStr NVARCHAR(100) SET @SearchStr = '3330'  
-- <''>  Use equals for exact matches or like for finding it in a string.
DECLARE @Operator NVARCHAR(100) SET @Operator = 'equals' 
-- <1> If 1 it searches only columns that are of type 'char', 'varchar', 'nchar', 'nvarchar' 
DECLARE @SearchStringsOnly INT SET @SearchStringsOnly = 0 
-- Search only columns that contain this text in their name.  if blank it searches all columns
DECLARE @InColumnName NVARCHAR(100) SET @InColumnName = 'ID' 
/*****************************************************************/

DECLARE @TableName nvarchar(256) SET @TableName = '' 
DECLARE @ColumnName nvarchar(128) SET @ColumnName = ''

CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))

SET NOCOUNT ON

SET @SearchStr  = CASE WHEN @Operator = 'equals' 
                       THEN QUOTENAME('' + @SearchStr + '','''') 
                       ELSE QUOTENAME('%' + @SearchStr + '%','''') END 

WHILE @TableName IS NOT NULL 
BEGIN
 SET @ColumnName = ''
 SET @TableName = 
 (
  SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  FROM  INFORMATION_SCHEMA.TABLES
  WHERE  TABLE_TYPE = 'BASE TABLE'
   AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
   AND OBJECTPROPERTY(
     OBJECT_ID(
      QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
       ), 'IsMSShipped'
            ) = 0
   --AND QUOTENAME(TABLE_NAME) NOT LIKE '%%'
   
 )

 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
 BEGIN
  SET @ColumnName =
  (
   SELECT MIN(QUOTENAME(COLUMN_NAME))
   FROM  INFORMATION_SCHEMA.COLUMNS
   WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
    AND TABLE_NAME = PARSENAME(@TableName, 1)
    AND ((@SearchStringsOnly = 1 and DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar'))
         or @SearchStringsOnly = 0)
    AND QUOTENAME(COLUMN_NAME) > @ColumnName
    AND COLUMN_NAME like '%' + @InColumnName + '%'
  )

  IF @ColumnName IS NOT NULL
  BEGIN
   INSERT INTO #Results
   EXEC
   (
    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
    FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
   )
   PRINT N'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
    FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
  END
 END 
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

We have some pretty large databases in our system and at times I need to find all columns in a database that have the same name.

DECLARE @ColName VARCHAR(200) = '%cert%'

SELECT Obj.Name AS [TableName], Col.name AS [ColumnName]
FROM DBO.SysObjects  Obj
INNER JOIN DBO.SysColumns Col ON Col.Id = Obj.Id
WHERE Col.Name LIKE @ColName

Here are my results for all columns that have “cert” in the name.