NSSM : Starting applications on startup

Great tool to start applications on startup. NSSM : The Non sucking service manager.

Cursor : Loop Tables

Cursor to loop tables.

I use this pattern a lot in DBA  scripts...

DECLARE @SQL NVARCHAR(1000)
DECLARE @input_schema VARCHAR(100)
DECLARE @input_table VARCHAR(100)
DECLARE @current_schema VARCHAR(100)
DECLARE @current_table VARCHAR(100)

SET @input_schema = null
SET @input_table = null

DECLARE tablecursor CURSOR FOR
  SELECT table_schema, table_name
  FROM   information_schema.TABLES
  WHERE  TABLE_TYPE = 'BASE TABLE'
    AND  (TABLE_SCHEMA = @input_schema OR (@input_schema = '' OR @input_schema is null))
    AND  (TABLE_NAME = @input_table OR (@input_table = '' OR @input_table is null))

OPEN tablecursor

FETCH NEXT FROM tablecursor INTO @current_schema, @current_table

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'EXEC add_to_table_queue '+ @current_schema + ' , ' + @current_table

      PRINT @SQL
      --EXEC Sp_executesql @SQL

      FETCH NEXT FROM tablecursor INTO @current_schema, @current_table
  END

CLOSE tablecursor
DEALLOCATE tablecursor 

SQL 2000 : Online & Offline databases

I can't believe I still need these in 2019...

-- SQL 2000 : Offline databases
select * from master..sysdatabases
where status & 512 = 512
and dbid > 4

-- SQL 2000 : Online databases
select * from master..sysdatabases
where status & 512 <> 512
and dbid > 4

TSQL : Loop a date range

Useful for Incremental processing...

DECLARE @minDate DATE
SET @minDate = '1997-02-13'
DECLARE @maxDate DATE
SET @maxDate = GETDATE()
 
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @counterDate DATE
DECLARE @counterDateText VARCHAR(30)
DECLARE @MinCount INT
DECLARE @Stepsize INT
 
DECLARE @stepstart DATETIME
DECLARE @stepend DATETIME
DECLARE @duration bigint
DECLARE @message varchar(200)

SELECT @Counter = DATEDIFF(dd,@minDate,@maxDate)
SELECT @MinCount = 0
SELECT @Stepsize = 1
 
WHILE @Counter >= @MinCount 
 
BEGIN
SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
--RAISERROR (@CounterText, 10, 1) WITH NOWAIT  
 
SET @counterDate = DATEADD(dd,-@counter,@maxDate)
SET @counterDateText = CONVERT(VARCHAR(30),@counterDate,120)

RAISERROR (@counterDateText, 10, 1) WITH NOWAIT  

SET @stepstart = GETDATE()


-- do useful stuff here
-- WAITFOR DELAY '00:00:01'

SET @stepend = GETDATE()

SET @duration = DATEDIFF(ss,@stepstart,@stepend)
SET @message = 'Took ' + CONVERT(varchar(20),@duration) + ' seconds'
RAISERROR (@message, 10, 1) WITH NOWAIT  

 
SELECT @Counter = @Counter - @Stepsize 
END

TED Talk : Forget the pecking order at work

This TED talk feels rather relevant right now 

Link : Forget the pecking order at work

Manually Checking Table Statistics

How to examine table statistics and update them if necessary

UPDATE STATISTICS  dbo.Study WITH FULLSCAN

DBCC SHOW_STATISTICS ('dbo.study','pk_study')

SELECT COUNT(*) AS [RowsInTable] FROM dbo.study

findInName - Stored Procedure to search all object names

-search all databases
CREATE OR ALTER PROC tools.findInName
(@searchstring VARCHAR(200))
AS
BEGIN
SET NOCOUNT ON
DECLARE @results TABLE (
    [search_term] VARCHAR(255),
    [database_name] VARCHAR(255),
    [schema_name] VARCHAR(255),
    [object_Name] VARCHAR(255),
 [type_desc] VARCHAR(255) )

DECLARE @dbname sysname;
DECLARE @actualsearchstring VARCHAR(200);

SET @actualsearchstring = REPLACE(@searchstring,'_','/_')

DECLARE @dsql nvarchar(max) = '
SELECT  ''' +@searchstring + '''  AS [search_term]
     ,DB_NAME() AS [database_name]
  ,TABLE_SCHEMA
  ,TABLE_NAME
  ,TABLE_TYPE 
 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE ''%'+@actualsearchstring+'%'' ESCAPE ''/''

UNION ALL

SELECT   ''' +@searchstring + '''  AS [search_term]
     ,DB_NAME() AS [database_name]
     ,ROUTINE_SCHEMA
  ,ROUTINE_NAME
  ,ROUTINE_TYPE 
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME LIKE ''%'+@actualsearchstring+'%'' ESCAPE ''/''

';
DECLARE @execCommand nvarchar(max);

DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY 
FOR
SELECT name FROM sys.databases (NOLOCK)
OPEN dbs;
FETCH NEXT FROM dbs INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @execCommand = 'EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @dsql';

    INSERT @results
    EXEC sys.sp_executesql @execCommand, N'@dsql nvarchar(max)', @dsql

    FETCH NEXT FROM dbs INTO @dbname; 
END

CLOSE dbs;
DEALLOCATE dbs;
SELECT * FROM @results
END
GO

EXEC tools.findInName '_'