Category Archives: Sql

Use this to see what you or others were recently doing.

USE [master]
GO
SELECT [execquery].[last_execution_time],
[execsql].[text]  
FROM [sys].[dm_exec_query_stats] AS [execquery]
CROSS APPLY [sys].[dm_exec_sql_text]([execquery].[sql_handle]) AS [execsql]
WHERE [last_execution_time] > DATEADD(HOUR, -1, GETDATE())
AND [execsql].[text] LIKE '%execq%'
ORDER BY [execquery].[last_execution_time] DESC

We can do this checking the default trace.

--
-- Review Database File Growth
--
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = [path]
FROM [sys].[traces]
WHERE [is_default] = 1
SELECT [StartTime],
DB_NAME([databaseid]) AS [DatabaseName],
[Filename],
SUM(([IntegerData] * 8) / 1024) AS [Growth in MB],
([Duration] / 1000) AS [Duration in seconds],
RIGHT('0' + CAST(([Duration] / 1000) / 3600 AS VARCHAR), 2) + ':' + RIGHT('0'
+ CAST((([Duration] / 1000) / 60) % 60 AS VARCHAR),
2) + ':' + RIGHT('0'
+ CAST(([Duration] / 1000)
% 60 AS VARCHAR), 2) [TimeHHMMSS]
FROM ::
FN_TRACE_GETTABLE(@trcfilename, DEFAULT)
WHERE ([EventClass] = 92
OR [EventClass] = 93
)
--AND StartTime >= DATEADD(WEEK, -1,GETDATE())
GROUP BY [StartTime],
[Databaseid],
[Filename],
[IntegerData],
[Duration]
ORDER BY [StartTime]

What will LTRIM and RTRIM do with CR LF or CRLF?

IF OBJECT_ID('tempdb.dbo.[#testtable]') IS NOT NULL
DROP TABLE [#testtable]

CREATE TABLE [#testtable] ([TestCRLF] VARCHAR(50))

INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(13) + CHAR(10)
)
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(13)
)
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(10)
)

SELECT [TestCRLF] + 'Z' AS NoPostProcessing
FROM [#testtable]

SELECT LTRIM(RTRIM([TestCRLF])) + 'Z' AS Trims
FROM [#testtable]

SELECT RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([TestCRLF], CHAR(13) + CHAR(10), ' '), CHAR(10) + CHAR(13), ' '), CHAR(13), ' '),
CHAR(10), ' ')) + 'Z' ReplaceAndTrim
FROM [#testtable]

Here’s what the output looks like:

Of course if we output to text the Z character would be in the next line. Since we are trying to clean up data with spaces or CRLF this shows a good demonstration of the problem.

(Thanks to Chris Trump!)

Find users who don’t have a login. These are real orphans. We find by name because they might be out of sync by id. We assume that’s already been checked.
One caution is some users may have login access via an Active Directory group. Do not run the DROP statements without testing the impact.

SELECT  [sp].[name] AS [LoginName],
        [dp].[name] AS [UserName],
        [dp].[principal_id],
        [dp].[type],
        [dp].[type_desc],
        [dp].[default_schema_name],
        [dp].[create_date],
        [dp].[modify_date],
        [dp].[owning_principal_id],
        [dp].[sid],
        [dp].[is_fixed_role],
        [dp].[sid],
   --     'DROP SCHEMA [' + QUOTENAME([dp].[name]) + '];' + CHAR(10) +
        'DROP USER ' + QUOTENAME([dp].[name]) + ';'
FROM    [sys].[database_principals] [dp]
LEFT OUTER JOIN [sys].[server_principals] [sp]
ON      [sp].[sid] = [dp].[sid]							-- sid could be incorrectly mismatched 
        AND CHARINDEX([dp].[name], [sp].[name]) > 0		-- here we are trying to match by login NAME which makes sense. Occasionally users are created without domain so catch those too.
WHERE   [dp].[type] IN ('U', 'G')
        AND [dp].[principal_id] <> 1
        AND [sp].[name] IS NULL
ORDER BY [UserName]