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