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