Rebuild Indexes of all tables in a single MS SQL database

0
2 months ago
by suhailkaleem in ,
  1. Rebuilds indexes on smallest tables first, allowing the maximum number of indexes to be rebuilt in the shortest amount of time.
  2. Real time progress updates, allowing you to estimate how much time is remaining before completion.
  3. Correctly handles multiple schemas, a common flaw in other scripts.
SET NOCOUNT ON
GO

--Set the fillfactor
DECLARE @FillFactor TINYINT
SELECT @FillFactor=80

DECLARE @StartTime DATETIME
SELECT @StartTime=GETDATE()

if object_id('tempdb..#TablesToRebuildIndex') is not null
begin
drop table #TablesToRebuildIndex
end

DECLARE @NumTables VARCHAR(20)

SELECT
s.[Name] AS SchemaName,
t.[name] AS TableName,
SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM
sys.schemas s
LEFT JOIN sys.tables t
ON  s.schema_id = t.schema_id
LEFT JOIN sys.partitions p
ON  t.object_id = p.object_id
LEFT JOIN sys.allocation_units a
ON  p.partition_id = a.container_id
WHERE
p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
AND p.rows IS NOT NULL
AND a.type = 1  -- row-data only , not LOB
GROUP BY
s.[Name],
t.[name]
SELECT @NumTables=@@ROWCOUNT

DECLARE RebuildIndex CURSOR FOR
SELECT
ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
ttus.SchemaName,
ttus.TableName,
ttus.RowsInTable
FROM
#TablesToRebuildIndex AS ttus
ORDER BY
ttus.RowsInTable
OPEN RebuildIndex

DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
RAISERROR (@Status, 0, 1) WITH NOWAIT  --RAISERROR used to immediately output status

SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
EXEC sp_executesql @Statement

FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
END

CLOSE RebuildIndex
DEALLOCATE RebuildIndex

drop table #TablesToRebuildIndex

Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'

GO
sql

extract date from datetime and then compare with date field

0
6 months ago
by admin in

MSSQL 2005 does not have DATE datatype only datetime and smalldatetime

datetime will return date and time

where as ms sql 2008 have DATE datatype which makes it easy to extract date

mysql also have DATE datatype

for mssql 2005

following is the code to extract  just the date part from datetime field

i will be using getdate function  to get current date time you can replace getdate() with datetime field name

Cast(Floor(Cast(GetDate() As Float)) As DateTime)

this will extract date from datetime field

and this how you compare

select * from YourTable where

Cast(Floor(Cast(GetDate() As Float)) As DateTime) > YourDateTImeField

Equivalent of ID IN in LINQ

0
7 months ago
by suhailkaleem in

The IN query will get results within the given range

 

SQL

select * from tablename where id in(12,34,56)

Equivalent LINQ TO SQL (VB.NET)

From c In db.tablename Where (New Integer() {12,34,56}).Contains(c.id)

Equivalent LINQ TO SQL (C#)

from c in db.tablenamewhere (new int[] {
12,
34,
56
}).Contains(c.id)

Go to Top