Monday, August 24, 2009

Reindex SQL Server Database

Sometimes you may need to reindex your database while you transport it from SQL 2000 to SQL 2005.

Execute the command below(Write your db name instead of mydatabasename)



DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
-- SQL 2000 command
--DBCC DBREINDEX(@Table,' ',@fillfactor)
-- SQL 2005 command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Read more...

Hide SQL Server Database(s)

Sometimes you may want to hide SQL Server databases from unauthorized users.(Like hosting firm etc.)
To show SQL databases to only authorized users follow the steps below;

Open New Query;

Execute the command below to remove "view all database(s) right" from public role;

USE master
GO
DENY VIEW any DATABASE TO public

Thsn Execute command below to append users to view and own database

USE database_name
GO
EXEC sp_changedbowner username

Read more...
 
span.fullpost {display:none;}