Delete/Disable Multiple SQL Server jobs

DECLARE @jobId binary(16),
@PK_Job INT,
@Counter TINYINT

DECLARE @Jobs TABLE
(
PK_Job INT IDENTITY(1,1),
job_id BINARY(16)
)
BEGIN
INSERT INTO @Jobs
(
job_id
)
SELECT job_id FROM msdb.dbo.sysjobs

SET @PK_Job = 0

WHILE 1 = 1
BEGIN
---- Looping through @Jobs Table
SELECT
TOP 1 @jobId = job_id,
@PK_Job = PK_Job
FROM
@Jobs
WHERE
PK_Job > @PK_Job
ORDER BY
PK_Job ASC

SELECT @Counter = @@ROWCOUNT
IF @Counter = 0
BREAK

IF (@jobId IS NOT NULL)
BEGIN
--For Disable un-comment the below code
--EXEC msdb.dbo.sp_update_job @jobId, @enabled = 0

--For Delete un-comment the below code
--EXEC msdb.dbo.sp_delete_job @jobId
PRINT @jobId
END
END

END

Space used to store data in Table and Index

create table #t
(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)

declare @id nvarchar(128)

declare c cursor for
SELECT name FROM sys.tables
WHERE SCHEMA_NAME(schema_id) = 'dbo' AND type='U'
open c
fetch c into @id

while @@fetch_status = 0 begin

insert into #t
exec sp_spaceused @id

fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

Query to change the column Type to another Type

DECLARE @SourceColumn NVARCHAR(MAX),
@DestinationColumn NVARCHAR(MAX)

SET @SourceColumn = 'VARCHAR'
SET @DestinationColumn = 'NVARCHAR'

SELECT
'Alter Table ' + OBJECT_NAME(c.OBJECT_ID) + ' Alter Column ' +
c.name + @DestinationColumn + '(' + CONVERT(NVARCHAR(MAX),c.max_length) + ')'
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE OBJECT_ID = OBJECT_ID('tb_test')
AND t.NAME = @SourceColumn

Attach Database without log file and with multiple files

Attach Database without log file and with multiple files

CREATE DATABASE [InitialLoad] ON
( FILENAME = N'E:\SQLDATA\InitialLoad.mdf'),
(FILENAME = N'E:\SQLDATA\InitialLoad.adf'),
(FILENAME = N'E:\SQLDATA\InitialLoad.idf' )
FOR ATTACH_REBUILD_LOG

Shrink Database Files

ALTER DATABASE --DatabaseName--
SET RECOVERY SIMPLE;

SELECT name FROM sys.database_files

DBCC SHRINKFILE (--name--, 3);

ALTER DATABASE --DatabaseName--
SET RECOVERY FULL;