Lang: T-SQL

SqlServer.CheckTaskCompletion.sql

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests;

SqlServer.Function.CharlistToTable.sql

CREATE FUNCTION [dbo].[iter_charlist_to_table]
(
     @list      ntext,
     @delimiter nchar(1 ) = N','
)
RETURNS @tbl TABLE
(
     listpos INT IDENTITY (1, 1) NOT NULL,
     STR     varchar (4000),
     NSTR    nvarchar(2000 )
)
AS
BEGIN

     DECLARE
          @pos      INT,
          @textpos  INT,
          @chunklen smallint,
          @tmpstr   nvarchar(4000 ),
          @leftover nvarchar(4000 ),
          @tmpval   nvarchar(4000 )

     SET @textpos = 1
     SET @leftover = ''

     WHILE @textpos <= DATALENGTH( @list) / 2
     BEGIN
          SET @chunklen = 4000 - DATALENGTH(@leftover ) / 2
          SET @tmpstr = @leftover + SUBSTRING(@list , @textpos, @chunklen)
          SET @textpos = @textpos + @chunklen

          SET @pos = CHARINDEX( @delimiter, @tmpstr )

          WHILE @pos > 0
          BEGIN
               SET @tmpval = LTRIM( RTRIM(LEFT(@tmpstr , @pos - 1)))

               INSERT @tbl (STR, NSTR) VALUES (@tmpval , @tmpval)

               SET @tmpstr = SUBSTRING( @tmpstr, @pos + 1, LEN( @tmpstr))
               SET @pos = CHARINDEX( @delimiter, @tmpstr )
          END

          SET @leftover = @tmpstr
     END

     INSERT @tbl (STR, NSTR) VALUES (LTRIM (RTRIM( @leftover)), LTRIM(RTRIM (@leftover)))
     RETURN

END

SqlServer.Function.StripHtmlTags.sql

CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR (MAX) AS
BEGIN
   DECLARE @Start INT;
    DECLARE @End INT;
    DECLARE @Length INT;

    SET @Start = CHARINDEX ('<', @HTMLText);
        SET @End = CHARINDEX( '>', @HTMLText , CHARINDEX ('<', @HTMLText));
        SET @Length = (@End - @Start) + 1;

        WHILE @Start > 0 AND @End > 0 AND @Length > 0
        BEGIN
               SET @HTMLText = STUFF( @HTMLText, @Start , @Length, '');
               SET @Start = CHARINDEX('<', @HTMLText );
               SET @End = CHARINDEX('>', @HTMLText , CHARINDEX ('<', @HTMLText));
               SET @Length = (@End - @Start) + 1;
        END

        RETURN LTRIM (RTRIM( @HTMLText));
END
GO

SqlServer.GetAllTableSizes.sql

SELECT
    t. NAME AS TableName,
    s. Name AS SchemaName,
    p. rows AS RowCounts,
    SUM( a.total_pages ) * 8 AS TotalSpaceKB,
    SUM( a.used_pages ) * 8 AS UsedSpaceKB,
    (SUM (a. total_pages) - SUM (a. used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys. tables t
INNER JOIN      
    sys. indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys. partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys. allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys. schemas s ON t.schema_id = s.schema_id
WHERE
    t. NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t. Name, s.Name , p .Rows
ORDER BY
    [TotalSpaceKB] DESC
;

SqlServer.GetDaysInMonth.sql

DECLARE @StartDT DATETIME= '2013-01-01';
DECLARE @EndDT DATETIME = '2013-12-20';
 
WHILE @StartDT < @EndDT
BEGIN
	PRINT CONVERT(VARCHAR, MONTH(@StartDT)) + ': ' + CONVERT(VARCHAR, DATEDIFF(DAY, DATEADD(DAY, 1 - DAY(@StartDT), @StartDT), DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(@StartDT), @StartDT))));
	SET @StartDT = DATEADD(MONTH, 1, @StartDT);
END

SqlServer.GetExecutionTime.sql

DECLARE @startproc DATETIME;
DECLARE @endproc DATETIME;
DECLARE @time INTEGER;
 
SELECT @startproc = GETDATE();
exec <PROCEDURE>
SELECT @endproc = GETDATE();
SELECT @time = DATEDIFF(MILLISECOND, @startproc, @endproc);
 
PRINT STR(@time);

SqlServer.GetOwnerUsername.sql

CREATE PROCEDURE Get_OwnerUsername
WITH EXECUTE AS OWNER
AS
SELECT SUSER_SNAME()
GO

SqlServer.GetResourceNames.sql

/* GET PROCEDURES */
SELECT ROUTINE_NAME
FROM DATABASE.information_schema.routines
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME

/* GET TABLES */
SELECT TABLE_NAME
FROM DATABASE.information_schema.tables
ORDER BY TABLE_NAME

SqlServer.GetStartEndOfWeek.sql

DECLARE @TwoWeeksAgoDate DATE;
SET @TwoWeeksAgoDate = DATEADD(ww, -2, GETDATE())

DECLARE @StartOfWeek DATE;
SET @StartOfWeek = DATEADD(DAY, 1 - DATEPART(WEEKDAY, @TwoWeeksAgoDate), @TwoWeeksAgoDate)

DECLARE @EndOfWeek DATE;
SET @EndOfWeek = DATEADD(DAY, 6, @StartOfWeek)

SELECT
	@TwoWeeksAgoDate 'TwoWeeksAgo',
	@StartOfWeek 'StartOfWeek',
	@EndOfWeek 'EndOfWeek'
;

SqlServer.LongPrint.sql

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)
AS
/*
Example:
exec LongPrint @string =
'This String
Exists to test
the system.'
*/
/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).
It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.
If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.
If it is passed a null value, it will do virtually nothing.
NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */
DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */
set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))
WHILE LEN(@String) > 1
BEGIN
    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN
           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END   
    PRINT SUBSTRING(@String, 1, @CurrentEnd) 
    set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)   
END
GO

SqlServer.RenameDbInMultiuser.sql

ALTER DATABASE DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
GO
EXEC sp_renamedb N'OLD_DB_NAME', N'NEW_DB_NAME';
GO
ALTER DATABASE DB SET MULTI_USER;

SqlServer.RepairData.sql

ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKTABLE ('TABLE_NAME', REPAIR_ALLOW_DATA_LOSS)
DBCC CHECKDB(DB_NAME, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DB_NAME SET MULTI_USER;