EXEC MASTER..xp_fixeddrives
Extract data from nvarchar column
The case:
We have a log table that include Message column (nvarchar).
We need to extract from the message some data, for example: Entity ID and Entity Type.
SELECT LogId,
(case when CHARINDEX ('EntityID:', Msg) > 0
then substring ( Msg,
CHARINDEX ('EntityID:', Msg) + 9, -- 9=length of 'EntityID:'
( case when CHARINDEX ('EntityID:', Msg) > 0
then ( CHARINDEX (',', Msg, CHARINDEX ('EntityID:', Msg))
- CHARINDEX ('EntityID:', Msg) - 9) -- 9=length of 'EntityID:'
else CHARINDEX ('EntityID:', Msg) + 9 -- 9=length of 'EntityID:'
end
)
)
else NULL
end ) as EntityID,
(case when CHARINDEX ('EntityType:', Msg) > 0
then substring ( Msg,
CHARINDEX ('EntityType:', Msg) + 11, -- 11=length of 'EntityID:'
( case when CHARINDEX ('EntityType:', Msg) > 0
then ( CHARINDEX (',', Msg, CHARINDEX ('EntityType:', Msg))
- CHARINDEX ('EntityType:', Msg) - 11) -- 11=length of 'EntityID:'
else CHARINDEX ('EntityType:', Msg) + 11 -- 11=length of 'EntityID:'
end
)
)
else NULL
end ) as EntityType
FROM OurLogTable
Find out who modified an object in SQL Server
SELECT --T.*, 'I', I.*, 'E', E.*
E.trace_event_id, E.category_id, E.name,
T.[path], T.start_time, T.last_event_time, T.event_count,
I.NTUserName, I.NTDomainName, I.HostName,
I.ClientProcessID, I.ApplicationName,
I.LoginName, I.SPID, I.ObjectID, I.ServerName,
I.ObjectName, I.DatabaseName, I.SessionLoginName,
I.TextData, I.BinaryData
FROM sys.traces T
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I
JOIN sys.trace_events E On I.eventclass = E.trace_event_id
WHERE T.id = 1
AND E.name = 'Object:Altered' -- or 'Object:Deleted' or ...
--AND ObjectName like '%AnObjectName%' -- <- filter for a specific object
ORDER BY T.start_time desc
Get columns list of an index
SELECT i.[object_id] as TableID, object_name(i.[object_id]) as TableName,
i.index_id as IndexID, i.[name] as IndexName,
i.is_primary_key, i.type_desc as IndexTypeDesc,
ic.column_id, c.[name] as column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.index_id = ic.index_id
AND i.[object_id] = ic.[object_id]
JOIN sys.columns c ON ic.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
-- optional filters:
WHERE object_name(i.[object_id]) = 'TableName'
WHERE i.[name] = 'IndexName'
WHERE c.[name] = 'ColumnName'
tempdb files and usage
-- space usage information for each data file in tempdb
SELECT *,
(user_object_reserved_page_count)*8 as usr_obj_kb,
(internal_object_reserved_page_count)*8 as internal_obj_kb,
(version_store_reserved_page_count)*8 as version_store_kb
FROM sys.dm_db_file_space_usage
GO
-- tempdb files
SELECT * FROM tempdb.sys.database_files
GO
Troubleshooting tempdb Space Usage:
Resets the wait statistics for an instance of SQL Server
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
Group by with Rollup
GROUP BY .... WITH ROLLUP
"with Rollup" uses to aggregate sub-groups in a group statement.
An example will be better than any explanation:
Subscribe to:
Posts (Atom)