Drive Details using TSQL

SET NOCOUNT ON

DECLARE @res INT, 

@objdets INT,

@drive INT, 

@getdrive VARCHAR(13), 

@totalsize VARCHAR(50), 

@freespace VARCHAR(50), 

@DriveLetter char(1)

CREATE TABLE ##Drivespacedetails

(

DriveLetter CHAR(1),

TotalSpace bigint,

FreeSpace bigint,

)

INSERT INTO ##Drivespacedetails(DriveLetter,FreeSpace) EXEC master.dbo.xp_fixeddrives

DECLARE  drivedetails CURSOR FOR SELECT DriveLetter FROM ##Drivespacedetails

OPEN drivedetails

FETCH NEXT FROM drivedetails INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

SET @getdrive = 'GetDrive("' + @DriveLetter + '")' 

         EXEC @res = sp_OACreate 'Scripting.FileSystemObject', @objdets OUTPUT 

         IF @res = 0  

         EXEC @res = sp_OAMethod @objdets, @getdrive, @drive OUTPUT 

         IF @res = 0  

         EXEC @res = sp_OAGetProperty @drive,'TotalSize', @totalsize OUTPUT 

    IF @res = 0  

         EXEC @res = sp_OAGetProperty @drive,'FreeSpace', @freespace OUTPUT 

         IF @res <> 0  

         EXEC sp_OADestroy @drive 

EXEC sp_OADestroy @objdets 

SET @totalsize = (CONVERT(BIGINT,@totalsize) / 1048576 )

SET @freespace = (CONVERT(BIGINT,@freespace) / 1048576 )

     update ##Drivespacedetails set TotalSpace=@totalsize,FreeSpace=@freespace where DriveLetter=@DriveLetter

FETCH NEXT FROM drivedetails INTO @DriveLetter

END

CLOSE drivedetails

DEALLOCATE drivedetails

SELECT @@servername as ServerName,DriveLetter,FreeSpace AS [FreeSpace MB], (TotalSpace - FreeSpace) AS [UsedSpace MB], TotalSpace AS [TotalSpace MB], ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##Drivespacedetails ORDER BY [DriveLetter] ASC

DROP TABLE ##Drivespacedetails

Determine which objects exist in a particular filegroup

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = f.data_space_id --* New FileGroup*

GO

How to find startup procedures in sql

SELECT ROUTINE_NAMEFROM MASTER.INFORMATION_SCHEMA.ROUTINESWHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION FROM SYS.CONFIGURATIONS WHERE NAME = 'scan for startup procs'GO

Drop Indexes in a Database

set nocount on

go

DECLARE @sql VARCHAR(2000),@DB VARCHAR(100),@count int,@TableName varchar(200),

@Index_Name varchar(200),@ConstraintName varchar(200)

SET @DB = db_name()

if exists(select * from msdb..sysobjects where name =

'DropIndexes_AllTables')

drop table msdb.dbo.DropIndexes_AllTables

CREATE TABLE msdb.dbo.DropIndexes_AllTables(tablename

varchar(200),ConstraintName varchar(200),Index_Name varchar(200),type

varchar(10))

SELECT @SQL = 'USE '+@db+char(13)+'SELECT object_name(parent_object_id)

TableName,sobj.Name ConstraintName,sidx.name Index_Name,type FROM sys.objects

sobj

join '+@db+'.dbo.sysindexes sidx on sobj.parent_object_id = sidx.id

where indid > 0 and type in

(''u'',''PK'',''UQ'') and

sidx.name not like ''_WA_sys_%'''

print @sql

insert into msdb.dbo.DropIndexes_AllTables

exec(@sql)

create table #temp_baby(ConstraintName varchar(200),TableName varchar(150))

select ConstraintName,TableName, Index_Name,Type from

msdb.dbo.DropIndexes_AllTables

insert into #temp_baby(ConstraintName,TableName)

select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables

select @count = count(*) from #temp_baby

while(@count > 0)

begin

select top 1 @ConstraintName = ConstraintName,@tablename = tablename from

#temp_baby

select @sql = 'USE '+@db+char(13)+'alter table '+@tablename+' drop

constraint '+@ConstraintName+''

print @sql

delete from #temp_baby where ConstraintName = @ConstraintName and tablename =

@tablename

select @count = count(*) from #temp_baby

end

insert into #temp_baby(ConstraintName,TableName)

select distinct ConstraintName,TableName from msdb.dbo.DropIndexes_AllTables

print '------'

delete from msdb.dbo.DropIndexes_AllTables where Index_Name in (select

ConstraintName from #temp_baby)

select @count = count(*) from msdb.dbo.DropIndexes_AllTables

while(@count > 0)

Begin

select top 1 @tablename = TableName,@Index_name = Index_Name from

msdb.dbo.DropIndexes_AllTables order by tablename

select @sql = 'USE '+@DB+';drop index

'+@tablename+'.'+@Index_name+''

print @sql

--print @tablename, @Index_name

delete from msdb.dbo.DropIndexes_AllTables where TableName = @tablename and

Index_Name = @Index_name

select @count = count(*) from msdb.dbo.DropIndexes_AllTables

end

drop table #temp_baby

Generate Create Index Scripts for a Database

-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''
SET @IXSQL = 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '

SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID

DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '

SET @IXSQL = @IXSQL + @IxColumn

FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn

SET @IXSQL = @IXSQL + ')'
-- Print out the CREATE statement for the index
PRINT @IXSQL

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX


-- Get all existing primary keys
DECLARE cPK CURSOR FOR
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
ORDER BY TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''
SET @PKSQL = 'ALTER TABLE ' + @PkTable + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY CLUSTERED ('

-- Get all columns for the current primary key
DECLARE cPKColumn CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = @PkTable AND CONSTRAINT_NAME = @PkName
ORDER BY ORDINAL_POSITION
OPEN cPKColumn

DECLARE @PkColumn SYSNAME
DECLARE @PkFirstColumn BIT SET @PkFirstColumn = 1
-- Loop through all columns and append the sql statement
FETCH NEXT FROM cPKColumn INTO @PkColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@PkFirstColumn = 1)
SET @PkFirstColumn = 0
ELSE
SET @PKSQL = @PKSQL + ', '

SET @PKSQL = @PKSQL + @PkColumn

FETCH NEXT FROM cPKColumn INTO @PkColumn
END
CLOSE cPKColumn
DEALLOCATE cPKColumn

SET @PKSQL = @PKSQL + ')'
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName
END
CLOSE cPK
DEALLOCATE cPK

Database Size Info

declare @dbname sysname

set nocount on

if @dbname is not null and @dbname not in (select name from sysdatabases (nolock))

begin

raiserror('Incorrect database name. ', 16, 1)

end

create table #datafilestats

(    dbname varchar(25),

    flag bit default 0,

    Fileid tinyint,

    [FileGroup] tinyint,

    TotalExtents dec (8, 1),

    UsedExtents dec (8, 1),

    [Name] varchar(50),

    [FileName] sysname )

declare @string sysname, @dbname1 sysname

set @string = ''

if @dbname is not null

    set @dbname1 = @dbname

else

    set @dbname1 = ''

set @dbname = ''

while 1=1

    begin

     select top 1 @dbname = name from master..sysdatabases where name > @dbname --order by name asc     

     if @@rowcount = 0

     break

     set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS with no_infomsgs'

    insert into #datafilestats (Fileid, [FileGroup] , TotalExtents , UsedExtents , [Name] , [FileName]) exec (@string)

update #datafilestats set dbname = @dbname, flag = 1 where flag = 0

    update #datafilestats set TotalExtents = (select sum(TotalExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)

        where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname

    update #datafilestats set UsedExtents = (select sum(UsedExtents)*8*8192.0/1048576.0 from #datafilestats where dbname = @dbname)

        where flag = 1 and Fileid = 1 and FileGroup = 1 and dbname = @dbname

    end

create table #sizeinfo 

( db_name varchar(30) not null primary key clustered,

total dec (7, 1),

data dec (7, 1),

data_used dec (7, 1),

[data (%)] dec (7, 1),

data_free dec (7, 1),

[data_free (%)] dec (7, 1),

log dec (7, 1),

log_used dec (7, 1),

[log (%)] dec (7, 1),

log_free dec (7, 1),

[log_free (%)] dec (7, 1),

status dec (7, 1) )    

insert #sizeinfo (db_name, log, [log (%)], status ) exec ('dbcc sqlperf(logspace) with no_infomsgs')

update #sizeinfo set

data = d.TotalExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1

update #sizeinfo set

data_used = d.UsedExtents from #datafilestats d join #sizeinfo s on d.dbname = s.db_name where d.flag = 1 and d.Fileid = 1 and d.FileGroup = 1

update #sizeinfo set

total = (data + log)

update #sizeinfo set

    [data (%)] = (data_used * 100.0 / data)

update #sizeinfo set

     data_free = (data - data_used)

update #sizeinfo set

     [data_free (%)] = (100 - [data (%)])

update #sizeinfo set

    log_used = (log * [log (%)] / 100.0)

update #sizeinfo set

    log_free = (log - log_used)

update #sizeinfo set

    [log_free (%)] = (log_free * 100.0 / log)

print ''

if @dbname1 = ''

begin

print 'Database size report on ' + @@servername + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)

    select db_name,

total,

data,

data_used,

[data (%)],

data_free,

[data_free (%)],

log,

log_used,

[log (%)],

log_free,

[log_free (%)]

from #sizeinfo order by db_name asc compute sum(total)    

end

else

begin

print 'Database size report on ' + @@servername + '.' + @dbname1 + ' as of ' + convert(varchar(30), getdate(), 100) + char(10)

    select db_name,

total,

data,

data_used,

[data (%)],

data_free,

[data_free (%)],

log,

log_used,

[log (%)],

log_free,

[log_free (%)]

from #sizeinfo where db_name = @dbname1    

end

go

drop table #datafilestats 

drop table #sizeinfo

Job Status Script

select b.name,a.step_name,

c.message,

case  c.run_status when 0 then 'Failed'

when 1 then 'Succeeded'

when 2 then 'Retry'

when 3 then 'Canceled'

when 4 then 'In progress'

else 'NO STATUS' END as status

,substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6), 1, 2)

            +    ':'

            + substring (

            right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.run_time), 6) ,3 ,2)

            +    ':'

            + substring (

            right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.run_time), 6) ,5 ,2) as run_time

,substring (right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8), 1, 4)

            +    '/'

            + substring (

            right (stuff (' ', 1, 1, '00000000') + convert(varchar(8), c.run_date), 8) ,5 ,2)

            +    '/'

            + substring (

            right (stuff (' ', 1, 1, '00000000') + convert(varchar(8),c.run_date), 8) ,7 ,2) as run_date

from msdb..sysjobsteps a, msdb..sysjobhistory c,msdb..sysjobs b

where a.step_id=c.step_id and a.job_id=c.job_id

and

a.last_run_date=c.run_date

and

a.last_run_time=c.run_time

and a.job_id=b.job_id

Script to Validate Subscribers for all articles and publication

declare @pubdb nvarchar(50)
declare @artobj nvarchar(100)
declare @owner nvarchar(50)
declare @params nvarchar(100)
declare @cnt1 nvarchar(50)
declare @cmd nvarchar(1000)
declare @cmd1 nvarchar(1000)
DECLARE pub_validate CURSOR FOR
select publisher_db,source_owner,source_object from distribution.dbo.MSarticles
open pub_validate
fetch next from pub_validate into @pubdb,@owner,@artobj
while @@fetch_status=0
begin
select @cmd = 'select @cnt=count(*) from '+@pubdb+'.'+@owner+'.'+@artobj
select @params=N'@cnt varchar(100) OUTPUT'
exec sp_executesql @cmd,@params,@cnt=@cnt1 output
--select @cnt1
select @cmd1='use '+@pubdb+CHAR(10)+'go'+CHAR(10)+'sp_table_validation @table='+@artobj+',@expected_rowcount='+@cnt1
select @cmd1
--exec sp_executesql @cmd1
fetch next from pub_validate into @pubdb,@owner,@artobj
end
close pub_validate
deallocate pub_validate