Tuesday, 19 March 2019

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

Thursday, 10 January 2019

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

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  

-- do useful stuff here

SELECT @Counter = @Counter - @Stepsize 
END

Friday, 3 August 2018

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

Tuesday, 31 July 2018

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 '_'

TSQL - Searching for Escaped Characters

Use the ESCAPE keyword to define a character to use to escape a literal. The _ underscore cannot usually be searched, to do so escape it like this.
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%/_%' ESCAPE '/'

findInCode - Stored Procedure to search code in all databases for a string

--search all databases
CREATE OR ALTER PROC tools.findInCode
(@searchstring VARCHAR(200))
AS
BEGIN
SET NOCOUNT ON
DECLARE @results TABLE (
     [searchTerm] VARCHAR(255)
    ,[databaseName] VARCHAR(255)
    ,[schemaName] VARCHAR(255)
    ,[objectName] VARCHAR(255)
 ,[typeDesc] VARCHAR(255) )
DECLARE @dbname sysname;
DECLARE @dsql nvarchar(max) = '
 SELECT ''' +@searchstring + '''  AS [search_term]
   ,DB_NAME() AS [databaseName]
   ,SCHEMA_NAME(schema_id) as schemaName
   ,o.name AS ObjectName
   ,o.type_desc AS typeDesc
 FROM sys.sql_modules m (NOLOCK)
 INNER JOIN sys.objects o (NOLOCK) ON m.object_id = o.object_id
 WHERE m.definition LIKE ''%'+@searchstring+'%''
';
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.findInCode 'findme'


Friday, 27 July 2018

Database Mirroring : TSQL Health Scripts

-- Dynamic Managment View of configuration information

SELECT db_name(sd.[database_id])           AS [Database Name]
      ,sd.mirroring_state                  AS [Mirror State]
      ,sd.mirroring_state_desc             AS [Mirror State]
      ,sd.mirroring_partner_name           AS [Partner Name]
      ,sd.mirroring_role_desc              AS [Mirror Role] 
      ,sd.mirroring_safety_level_desc      AS [Safety Level]
      ,sd.mirroring_witness_name           AS [Witness]
      ,sd.mirroring_connection_timeout AS [Timeout(sec)]
FROM sys.database_mirroring AS sd
WHERE mirroring_guid IS NOT null
ORDER BY [Database Name];

-- history of all mirored transactions in last 2 hours

USE msdb;

EXEC sp_dbmmonitorresults DatabaseName, 2, 0;



--os performance counters about mirroring

SELECT *,[counter_name] as CounterName,[cntr_value] as CounterValue
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ('%Database Mirroring%')
AND [instance_name]='mirroredDatabase'