richardbasile

;WITH files AS (
    select name
         , filename
         , size
         , fileproperty ( name, 'SpaceUsed' ) as used
      from sysfiles
)
select name
     , filename
     , convert ( decimal ( 12, 2 ), size / 128.000 )            as FileSizeMB

richardbasile

DECLARE @planguide nvarchar(max) = 'PlanGuide'
 
SELECT st.dbid, st.text, qs.*, qp.*
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    cross apply sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE CONVERT(date, last_execution_time) = CONVERT(date, getdate())
    AND st.text like '%query text%'
    AND st.text not like '%e971a5bc-cd6c-4465-9232-2cb419163112%'
 

richardbasile

SELECT TOP 100
       query_hash        AS [Query Hash]
     , COUNT(query_hash) AS [Plan Count]
     , (
           SELECT TOP 1 text
             FROM sys.dm_exec_query_stats qs
             CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
            WHERE pc.query_hash = qs.query_hash
       ) AS [Query Text]
  FROM sys.dm_exec_query_stats pc

richardbasile

SELECT st.text, qs.*, qp.*
  FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
 cross apply sys.dm_exec_query_plan(plan_handle) qp
 WHERE CONVERT(date, last_execution_time) = CONVERT(date, getdate())
   AND st.text like '%<snippet>%' -- change this value
   AND st.text not like '%e971a5bc-cd6c-4465-9232-2cb419163112%'
 order by creation_time desc

richardbasile

SELECT a.xtype, a.name, b.colid, b.text
  FROM sysobjects a
  JOIN syscomments b on b.id = a.id
  JOIN ( SELECT distinct id FROM syscomments
          WHERE text LIKE '%<snippet>%'
       ) c on c.id = b.id
 ORDER BY 2, 3

richardbasile

-- privileges
select [Grantee] = prin.name
     , [Grantee Type] = prin.type_desc
     , [Permission] = perm.permission_name
     , [State] = perm.state_desc
     , [Class] = perm.class_desc
     , [Object] = object_name(perm.major_id)
  from sys.database_principals prin
  left join sys.database_permissions perm on perm.grantee_principal_id = prin.principal_id
 

richardbasile

SELECT object_name(object_id)          AS tableName
     , [name]                          AS indexName
     , stats_date(object_id, index_id) AS statsUpdated
  FROM sys.indexes
 ORDER BY 1, 2

richardbasile

SELECT t.name as table_name, i.name as index_name, s.index_type_desc, s.avg_fragmentation_in_percent, s.page_count
  FROM sys.dm_db_index_physical_stats (
         DB_ID('<db name>')
       , NULL --OBJECT_ID('dbo.<table name>')
       , NULL
         -- NULL to view all indexes;
         -- otherwise, input index number
       , NULL -- NULL to view all partitions of an index
       , NULL -- 'DETAILED') as s
  join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

richardbasile

SELECT t.name AS [TableName]
     , i.name AS [IndexName]
     , i.is_primary_key
     , i.is_unique_constraint
     , i.type_desc
     , SUM(s.[used_page_count]) * 8 AS IndexSizeKB
  FROM sys.indexes i
  JOIN sys.tables t ON t.object_id = i.object_id
  JOIN sys.dm_db_partition_stats s ON s.object_id = i.object_id AND s.index_id = i.index_id
 GROUP BY t.name 

richardbasile

DECLARE @sp_who2 TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,