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

-- 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 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

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 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

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

;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

select t.name      AS table_name
     , i.name      AS index_name
     , i.type_desc AS index_type
     , i.is_unique
     , i.is_primary_key
     , s.user_seeks
     , s.user_scans
     , s.user_lookups
     , s.user_updates
     , (SELECT MAX(ts) FROM (

richardbasile

;WITH details AS(
select i.object_id
     , upper(t.name) as table_name
     , i.index_id
     , i.name as index_name
     , i.is_unique_constraint
     , i.is_primary_key
     , CASE is_primary_key WHEN 1 THEN 'PK_'
       ELSE
         CASE is_unique_constraint

richardbasile

select 'exec sp_rename N''dbo.[' + fk.name + ']'', N''FK_' + upper(t.name) + '_' + lower(c.name) + ''', @objtype = ''OBJECT'';'
  from sys.foreign_keys fk
  join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
  join sys.columns c on c.object_id = fk.parent_object_id
                    and c.column_id = fkc.parent_column_id
  join sys.tables t on t.object_id = fk.parent_object_id
 where fk.name <> 'FK_' + upper(t.name) + '_' + lower(c.name)