Switch/Case with Substring

SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (@intMonth * 4) - 3, 3)

grant execute to user for all stored procedures

 

OPEN crsr

declare @proc varchar(100)

declare @stmt nvarchar(200)

 

fetch next from crsr into @proc

while @@fetch_status=0

begin

set @stmt='grant execute on ['+@proc+'] to username'

exec SP_EXECUTESQL @STMT

print @stmt

fetch next from crsr into @proc

end

 

close crsr

deallocate crsr

All Table sizes and row counts

 

 

create table #tmp1 (

      Servername SysName,

      DBName SysName,

      object_id int,

      ScheamaName sysName,

      TableName sysName,

      TableSize_KB int,

      row_count int

)

 

create table #tmp2 (

      Servername SysName,

      DBName SysName,

      object_id int,

      ScheamaName sysName,

      TableName sysName,

      row_count int

)

 

exec sp_msforeachdb 'use ?;

if DB_ID()>4 begin

      insert #tmp1

      select @@ServerName,DB_Name(),ps.object_id,

                  schema_name(o.schema_id) as SchemaName,

                  o.name as TableName,

                  SUM(ps.reserved_page_count)*8 as TableSize_KB,

                  0 as row_count

      from sys.dm_db_partition_stats ps

      join sys.objects o on o.object_id = ps.object_id

      where o.type=''U''

      group by ps.object_id,o.schema_id, o.name

 

      insert #tmp2

      select @@ServerName,DB_Name(),ps.object_id,

                  schema_name(o.schema_id) as SchemaName,

                  o.name as TableName,   

                   sum(ps.row_count) as row_count

      from sys.dm_db_partition_stats ps

      join sys.objects o on o.object_id = ps.object_id

      where o.type=''U'' and ps.index_id in (0,1)

      group by ps.object_id,o.schema_id, o.name

end

';

 

 

update t1

set t1.row_count=t2.row_count

from #tmp1 t1

left join #tmp2 t2 on

      t2.ServerName=t1.ServerName and

      t2.DBName=t1.DBName and

      t2.object_id=t1.object_id

 

 

select * from #tmp1

order by TableSize_KB desc

 

drop table #tmp1

drop table #tmp2

Which Indexes Are Being not Used-Which Tables Are Being not Used

 

SELECT

    QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS TableName,

    i.name AS IdxName,

    i.type_desc AS IdxType,

    ius.user_seeks,

    (ius.user_seeks*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Seek],

    ius.user_scans,

    (ius.user_scans*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Scan],

    ius.user_lookups,

    (ius.user_lookups*1.) / NULLIF((ius.user_seeks+ius.user_scans+ius.user_lookups),0) AS [%Lookup],

    ius.user_updates

FROM sys.indexes i

LEFT JOIN sys.dm_db_index_usage_stats ius

    ON  ius.object_id = i.object_id

        AND ius.index_id = i.index_id

        AND database_id = DB_ID()--Current DB

INNER JOIN sys.tables t

    ON t.object_id = i.object_id

INNER JOIN sys.schemas s

    ON t.schema_id = s.schema_id

WHERE

    t.type = 'U'

    AND t.is_ms_shipped = 0

ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

SELECT

    DB_NAME(ius.database_id) AS DBName,

    OBJECT_NAME(ius.object_id) AS TableName,

    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed   

FROM sys.indexes i

INNER JOIN sys.dm_db_index_usage_stats ius

    ON  ius.object_id = i.object_id

        AND ius.index_id = i.index_id

WHERE

    ius.database_id = DB_ID()

GROUP BY

    DB_NAME(ius.database_id),

    OBJECT_NAME(ius.object_id)

ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

 

TableAdapter Call Stored Procedure using Temp Table Error

when using tableadapter with Stored Procedure visual studio throw exception can’t find object #table

solution is in SP

SET FMTONLY OFF;

SSRS Row Group and Column Group RowNumber

 

normal way

=IIF(RowNumber(Nothing) Mod 2, "AliceBlue", "White")

grouped way

=IIF(RunningValue(Fields!STORE.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")

size and row count of top 100 tables

create table #t(d nvarchar(MAX),t nvarchar(MAX),r int,x nvarchar(100),s nvarchar(100),y nvarchar(100),z nvarchar(100))

declare @s nvarchar(MAX)

set @s=replace('if !~! not in (!master!,!model!,!msdb!,!tempdb!) exec [~].dbo.sp_msforeachtable "insert into #t(t, r,x,s,y,z) exec [~].dbo.sp_spaceused !?!"','!',char(39))

EXEC sp_MSForEachDB @command1=@s, @command2="update #t set d='~' where d is null", @replacechar='~'

select top(100) d as base, t as [table], s as size, r as rows from #t order by Cast(LEFT(s,len(s)-3) as int) desc

drop table #t

date range with recursive cte

WITH

DATE_RANGE as (

      SELECT

            [date]=GETDATE()

       UNION ALL

       SELECT DATEADD(day,1,[date])

       FROM DATE_RANGE

      WHERE DATEADD(day,1,[date])<=DATEADD(day,10,GETDATE())

)

SELECT * FROM DATE_RANGE