Where are tables located in database files?

\ by

This uses indexes to locate the files.


SELECT  [o].[crdate],
        DATEDIFF(MINUTE, [o].[crdate], GETDATE()) AS [MinAgo],
        [o].[name] AS [Tablename],
        [i].[indid],
        [i].[name] AS [IndexName],
        [i].[rowcnt],
        [i].[groupid],
        [f].[name] AS [FileGroupName],
        [d].[file_id],
        [d].[physical_name],
        [s].[name] AS [DataSpace]
FROM    [sys].[sysobjects] [o]
JOIN    [sys].[objects] [so]
ON      [so].[object_id] = [o].[id]
JOIN    [sys].[sysindexes] [i]
ON      [i].[id] = [o].[id]
JOIN    [sys].[filegroups] [f]
ON      [f].[data_space_id] = [i].[groupid]
JOIN    [sys].[database_files] [d]
ON      [f].[data_space_id] = [d].[data_space_id]
JOIN    [sys].[data_spaces] [s]
ON      [f].[data_space_id] = [s].[data_space_id]
WHERE   [is_ms_shipped] = 0
        AND [i].[name] IS NOT NULL
ORDER BY [Tablename],
        [IndexName],
        [d].[file_id]
Posted in: Coding, Configuration, SQL

SQL: Where are the indexes located?

\ by

Where are the indexes located?

SELECT  'table_name' = OBJECT_NAME(i.id),
        i.indid,
        'index_name' = i.name,
        i.groupid,
        'filegroup' = f.name,
        'file_name' = d.physical_name,
        'dataspace' = s.name
FROM    sys.sysindexes i,
        sys.filegroups f,
        sys.database_files d,
        sys.data_spaces s
WHERE   OBJECTPROPERTY(i.id, 'IsUserTable') = 1
        AND f.data_space_id = i.groupid
        AND f.data_space_id = d.data_space_id
        AND f.data_space_id = s.data_space_id
        AND i.name LIKE 'ndx%'
ORDER BY f.name,
        OBJECT_NAME(i.id),
        groupid

g

Posted in: Coding, Configuration

SQL: Last Day Of Month

\ by

For any date go back a month, then add a month to it.

SELECT  [dtMonthLastDay] = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)

Last day for any month etc. by Pinal Dave

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
Posted in: Coding

[master_admin].[dbo].[fn_RunDuration]

\ by 
CREATE FUNCTION [dbo].[fn_RunDuration] (@nRunDuration INT)
RETURNS VARCHAR(128)
AS 
BEGIN 
    DECLARE @vcMsg VARCHAR(128),
        @nH INT,
        @nM INT,
        @nS INT,
        @vcH VARCHAR(48),
        @vcM VARCHAR(48),
        @vcS VARCHAR(48)
    SELECT  @nH = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 6), 2),
            @nM = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 4), 2),
            @nS = RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 2),
            @vcH = CASE WHEN @nH = 0 THEN ''
                        WHEN @nH = 1 THEN CAST(@nH AS VARCHAR) + ' hour, '
                        ELSE CAST(@nH AS VARCHAR) + ' hours, '
                   END,
            @vcM = CASE WHEN @nM = 0 THEN ''
                        WHEN @nM = 1 THEN CAST(@nM AS VARCHAR) + ' minute, '
                        ELSE CAST(@nM AS VARCHAR) + ' minutes, '
                   END,
            @vcS = CASE WHEN @nS = 1 THEN CAST(@nS AS VARCHAR) + ' second'
                        ELSE CAST(@nS AS VARCHAR) + ' seconds'
                   END,
            @vcMsg = @vcH + @vcM + @vcS

    RETURN @vcMsg
END
Posted in: master_admin

[master_admin].[dbo].[fn_INTtoDateTime]

\ by 
CREATE FUNCTION [dbo].[fn_INTtoDateTime] (
     @vcDate VARCHAR(8),
     @vcTime VARCHAR(6)
    )
RETURNS DATETIME
AS 
BEGIN
    SET @vcDate = RIGHT(RTRIM('00000000' + CONVERT(CHAR(8), @vcDate) + ' '), 8)
    SET @vcTime = RIGHT(RTRIM('00000000' + CONVERT(CHAR(6), @vcTime) + ' '), 6)

    IF @vcDate = '00000000' 
        SET @vcDate = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), CAST(0 AS SMALLDATETIME), 121),
                                                   '-', ''), ':', ''),
                                   SPACE(1), ''), 8)
	
    RETURN (  
	CAST(SUBSTRING(@vcDate,1,4) + '-' + SUBSTRING(@vcDate,5,2) + '-' + SUBSTRING(@vcDate,7,2) + ' ' + 
		SUBSTRING(@vcTime,1,2) + ':' + SUBSTRING(@vcTime,3,2) + ':' + SUBSTRING(@vcTime,5,2) 
	AS DATETIME)
	)
END
Posted in: master_admin

SQL Agent: What ran last night after time …

\ by 
------------------------------------------------------------------------------------------------------------------------
--
-- What ran last night after time ...
--
SELECT  [master_admin].[dbo].[fn_INTtoDateTime]([run_date], [run_time]) AS [RunDate],
        DATENAME(WEEKDAY, CONVERT(VARCHAR(10), [run_date], 112)),
        [j].[name],
        [master_admin].[dbo].[fn_RunDuration]([run_duration]),
        [h].[step_id],
        [s].[step_name],
        [command] AS [StepCommand],
        [instance_id]
FROM    [msdb].[dbo].[sysjobhistory] [h] WITH (NOLOCK)
JOIN    [msdb].[dbo].[sysjobs] [j] WITH (NOLOCK)
ON      [j].[job_id] = [h].[job_id]
JOIN    [msdb]..[sysjobsteps] [s] WITH (NOLOCK)
ON      [s].[job_id] = [j].[job_id]
        AND [s].[step_id] = [h].[step_id]
WHERE   [run_date] >= CONVERT(VARCHAR(8), DATEADD(DAY, -0, GETDATE()), 112)

AND [h].[run_time] >= 021439
ORDER BY [RunDate]

Required objects
[fn_INTtoDateTime]
[fn_RunDuration]

 

Posted in: SQLAgent

What are all those old tempdb temp tables?

\ by

SQL Server caches the definitions of temp tables and table variables, to reduce the load on the system catalog in tempdb on busy systems which creates a lot of temp tables. Typically they have a name that starts with # and is followed by eight hex digits. –Erland Sommarskog

SQL Server Temporary Table Caching

This tip will describe the condition to allow caching of a SQL Server temporary table and a demonstration to benchmark the performance between a cached vs. non-cached temporary table.

Source: www.mssqltips.com/sqlservertip/4406/sql-server-temporary-table-caching/

 

 

Posted in: SQL Server

PowerShell: Order of command precedence

\ by

Here is the order of command precedence that is used by the command discovery process in Windows PowerShell:

· Alias: All Windows PowerShell aliases in the current session

· Filter/Function: All Windows PowerShell functions

· Cmdlet: The cmdlets in the current session (“Cmdlet” is the default)

· ExternalScript: All .ps1 files in the paths that are listed in the Path environment variable ($env:PATH)

· Application: All non-Windows-PowerShell files in paths that are listed in the Path environment variable

· Script: Script blocks in the current session

Posted in: Documentation, PowerShell