Monday, February 27, 2012

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

Tuesday, February 14, 2012

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