recursive unique identifier with CTE

;with CTE as(

      SELECt  ID=1,

                  GID=NEWID()

            UNION ALL

      SELECT  ID+1,

                  NEWID() FROM CTE WHERE ID<100

)

 

SELECT     

      *

FROM CTE OPTION (MAXRECURSION 100)

that’s all

recursive start and end of month

 

DECLARE     @StartDate DATE = '20231215';

 

WITH cteSource(MonthSerial)

AS (

      SELECT      DATEDIFF(MONTH, '19990101', @StartDate) AS MonthSerial

 

      UNION ALL

 

      SELECT      MonthSerial - 1 AS MonthSerial

      FROM  cteSource

      WHERE MonthSerial > 0

)

SELECT      MONTH(DATEADD(MONTH, MonthSerial, '19990101')) as IMONTH,

            CAST(DATEADD(MONTH, MonthSerial, '19990101')as DATE) AS STARTOFMONTH,

            CAST(DATEADD(MONTH, MonthSerial, '19990131')as DATE) AS ENDOFMONTH

FROM  cteSource

OPTION      (MAXRECURSION 0)

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