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
Monday, February 27, 2012
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
(
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
Subscribe to:
Posts (Atom)