Save All Indexes metadata of your database

Searching in my old vault of scripts I found a project where we needed to save all the indexes of a database in a table. Now is very easy to do the same process using smo instead of sql scripts or just using SSMS, but, this is a good example of how things used to be....
if not exists ( select * from sysobjects where ID =object_id(N'tblDBAIdxMetadt') and ObjectProperty(ID,'IsTable')=1)
CREATE TABLE [dbo].[tblDBAIdxMetadt](
    [TableId] [int] NOT NULL,
    [SchemaOwner] [varchar](25) NOT NULL,
    [TableName] [varchar](255) NOT NULL,
    [IndexID] [int] NOT NULL,
    [IsStatistics] [bit] NOT NULL DEFAULT ('FALSE'),
    [IsClustered] [bit] NOT NULL DEFAULT ('FALSE'),
    [IndexName] [varchar](255) NOT NULL,
    [ClusterType] [varchar](50) NOT NULL,
    [UniqueType] [varchar](50) NULL,
    [IndexType] [varchar](50) NULL,
    [AllColName] [varchar](2000) NULL
) ON [PRIMARY]

GO

DECLARE @MyID int
        ,@TableName varchar(255)
set @TableName = ---TableName Here            
SET @MyID = (SELECT OBJECT_ID(@TableName,'U')) 

INSERT INTO [dbo].[tblDBAIdxMetadt]
           ([TableId]
           ,[SchemaOwner]
           ,[TableName]
           ,[IndexID]
           ,[IsStatistics]
           ,[IsClustered]
           ,[IndexName]
           ,ClusterType
           ,[UniqueType]
           ,[IndexType]
           ,[AllColName])
SELECT
obj.id AS TableID
,usr.name Owner
,obj.name TableName
,idx.Indid AS IndexID
,CASE
 WHEN (idx.STATUS & 64) <> 0 THEN 1
 ELSE 0 end as IsStatistics
,Case idx.indid
  when 1 then 1
  else 0 end as IsClustered
 , CASE idx.name
  WHEN obj.name THEN '** NONE **'
 ELSE idx.name END AS IndexName,
  CASE idx.indid
  WHEN 1 THEN 'CLUSTERED'
  WHEN 0 THEN 'HEAP'
   ELSE 'NONCLUSTERED' END AS ClusterType,
            CASE
               WHEN (idx.status & 2048) > 0 THEN 'PRIMARY KEY'
                  WHEN (idx.status & (2|4096)) > 0 THEN 'UNIQUE'
             
            ELSE '' END AS UniqueType,
         CASE      
                  WHEN (idx.status & (2048)) > 0
                              OR ((idx.status & (4096)) > 0 )
                        THEN 'CONSTRAINT'
                  WHEN idx.indid = 0 THEN ' '
            ELSE 'INDEX' END AS IndexType,

            CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 1) IS NULL THEN ''
            ELSE '['+ INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 1)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 2) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,2)+']' END +
            CASE      
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 3) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,3)+']' END +
         CASE      
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 4) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,4)+']' END +
            CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 5) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,5)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 6) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,6)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 7) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 7)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 8) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,8)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 9) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,9)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 10) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,10)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 11) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,11)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 12) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,12)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 13) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,13)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 14) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,14)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 15) IS NULL THEN ''
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,15)+']' END +
      CASE      
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 16) IS NULL THEN ''
          ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,16)+']' END AS AllColName
    

 FROM sysobjects obj (NOLOCK)
      LEFT OUTER JOIN sysindexes idx (NOLOCK)
            ON obj.id = idx.id
     JOIN sysusers usr (NOLOCK)
            ON obj.uid = usr.uid
 WHERE obj.type = 'U' AND idx.indid < 255
 AND obj.name NOT IN ('dtproperties')
AND idx.name  NOT LIKE '_WA_Sys_%'
And obj.id = @MyID --Removed if want to query all tables
end

Batch Delete with WaitFor and Execution Time

Delete Tables

Today I got a crazy request from an ops dba, "Please create a script that will delete all the records of an specific table in batches of 50k and with a delay of x amount of time and this query can't run not more than 15 minutes at the time."
Eventually I created a store procedure that will take all the requirements as parameters and the ops dba got his happy face because the procedure is flexible enough to take a table name, delay time in seconds and finally an execution time.

Create procedure usp_YOurNameHere_del(@rowcount int = 50000, --#Records per Batch@ExecutionTime int = 15, --#minutes to execute process@dt int = 15, --#Dates target for deletetion@DelayLength char(8) = '00:00:03', --#delay in seconds before next match@delrecords int output --#Records deleted by process)as
Begin/* Variables declaration */declare @older datetime --#Target delete datedeclare @ExecutionDtTime datetime --Execution time declare @cntr int --#While Loop controllerdeclare @today datetime --#Today datetimedeclare
/*Variable Initialization */ @SqlCmd as varchar (max) --SqlScmd set @cntr = @rowcountset @today = getdate()set @older = dateadd (dd,(-1*@dt),@today)set @ExecutionDtTime = dateadd(mi,@ExecutionTime,@today)set
@delrecords = 0 /* set rowCount */Set rowcount @rowcount/* Begin try */begin try--validationsif not exists ( select * from information_schema.tables where table_name = 'YourTableNameHere' and table_schema = 'TableSchema')
16RAISERROR ('Table missing YourTableNameHere', , -- Severity.1 -- State.);
While @cntr = @rowcount and @ExecutionDtTime > @todayBeginBegin Transaction

yourTableNamehere s Delete sfrom With (tablockx, holdlock)inner join (
Id

YourTableNameHere select from with (nolock)
DateColumnHere


Where <= @older)aon a.Id = s.Id Select @cntr=@@rowcount


Commit set @delrecords = @delrecords + @cntrset @today = getdate()

WAITFOR DELAY @DelayLengthendprint 'Records Deleted: '+cast(@delrecords as varchar(8)) end
begin
try catch DECLARE @ErrorMessage NVARCHAR(4000);DECLARE @ErrorSeverity INT;DECLARE @ErrorState INT;



@ErrorMessage if @@trancount > 0 rollback SELECT = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();-- Use RAISERROR inside the CATCH block to return error-- information about the original error that caused-- execution to jump to the CATCH block.RAISERROR (@ErrorMessage, -- Message text.@ErrorSeverity, -- Severity.@ErrorState -- State.);end
catch end

Search Procedure results without getting any data

Today a junior developer was having an issue where he was to

research a store procedure to extract a few 10k records from 

the database,however, each time he ran the procedure he was 

getting a timeout because his criteria was too abroad.Eventually,

he ask me "How can I see all the columns returns by the procedure?"

by that time he didn't care about the data,So I said to him 

SET FMTONLY ON

that's the fastest way to see the return from any query or procedure

without getting any data.SEE MAGIC....