Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.

My alternative to scraps of paper lying about.

Others may find bits of it useful!



Tuesday, 24 December 2013

List all Stored Procedures within a db - SQL

SELECT
*

FROM
TFSheffieldNew.INFORMATION_SCHEMA.ROUTINES

WHERE
(ROUTINE_TYPE = 'PROCEDURE')

Monday, 28 October 2013

Auxiliary Numbers Table - Populated by CTE (Based on Itzik Ben-Gan)

USE [Auxiliary]
GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE
  [dbo].[Nums]
([n] [int] NOT NULL, PRIMARY KEY CLUSTERED
 ([n]  ASC ))
GO
;WITH x00(n) AS (SELECT 1 UNION ALL SELECT 1),
x02 (n) AS (SELECT 1 FROM x00 a, x00 b),
x04 (n) AS (SELECT 1 FROM x02 a, x02 b),
x08 (n) AS (SELECT 1 FROM x04 a, x04 b),
x16 (n) AS (SELECT 1 FROM x08 a, x08 b),
x32 (n) AS (SELECT 1 FROM x16 a, x16 b),
cTally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM x32)
 
INSERT INTO Nums(n)
SELECT * from cTally
WHERE n <= 1000000;
GO



 Based on the CTE used here:
http://sqlreportingservicescrystalreports.blogspot.co.uk/2009/12/auxilary-cte-of-numbers-sql.html

Monday, 28 January 2013

To obtain SQL Server 2008R2 Product Key

To obtain SQL Server 2008R2 Product Key:
  USE MASTER

GO



EXEC XP_REGREAD 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\100\BIDS\Setup','ProductCode'

GO

Tuesday, 20 November 2012

Convert time to string - SQL

Scenario where a db field contains seconds, the user wanted a SSRS report displaying various aggregates of the seconds field as Hours - Minutes - Seconds.


IFOBJECT_ID('dbo.udf_SCC_TimeToString') IS NOT NULL
DROP FUNCTION dbo.udf_SCC_TimeToString ;
GO

CREATE
FUNCTION dbo.udf_SCC_TimeToString
(
@timesec INT) --input in seconds as integer

RETURNSVARCHAR(25)

/*==========================================

Dom Horton

17/10/2012

==========================================*/

AS

 
BEGIN
DECLARE

@return
AS VARCHAR(25),

@a AS INT,

@b AS INT,

@hours AS INT,

@mins AS INT,

@secs AS INT;

SET@a = @timesec%3600 --get no. of seconds over the hour

SET@hours = (@timesec - @a)/3600 --get no. of seconds of completed hours, divide by hour in seconds to get completed hours

SET@b = @a --hold no. of secs over the hour

SET@a = @b%60--get no. of secs over the min

SET@mins = (@b - @a)/60 --get no. of seconds of completed mins, divide by min in secs to get completed minutes

SET@secs = @a

SET@return =

CASE
 
WHEN @timesec >=3600

THEN
 
convert(varchar(10),@hours) + ' hr ' +

convert(varchar(10),@mins) + ' min ' +

convert(varchar(10),@secs) + ' sec '

WHEN @timesec >=60

THEN

convert(varchar(10),@mins) + ' min ' +

convert(varchar(10),@secs) + ' sec '

ELSE

convert(varchar(10),@secs) + ' sec '

END;

RETURN
@return

END











Example below showing the Stored Procedure in use:



and in use in a SSRS report:

Thursday, 6 September 2012

Missing indexes - SQL

SELECT index_handle, database_id, object_id, equality_columns, inequality_columns, included_columns, statement

FROM sys.dm_db_missing_index_details