Central Management Server

Introduction:
A SQL Server CMS is just a central repository that holds a list of managed servers. SQL Server 2008 SSMS introduces a new feature, Multiple Server Query Execution, in Query Editor. Centralize the management and administration of a number of SQL Server instances from a single source can allow the DBA to save significant time and effort. This feature intends to increase the productivity of running same query against multiple servers at once. Some of usage includes:
• Configure group of servers
• Generate report or document from multiple servers
• Analyze result from multiple servers
• Run any SQL query against multiple servers

Pre-requisite:
1. SQL Server 2008 for Registering Central Management Server
2. SQL Server 2008 or SQL Server 2005 or SQL Server 2000 servers other than the registered Central Management Server.

To deploy or test Multiple Server Query Execution, you need to setup central management server in SSMS in SQL Server 2008.

How to:
To register a central management server and run the query against all the registered servers follow the below steps.

1. Open the “Registered Servers” from the “View” Menu in the management studio of SQL server 2008.

2. Right click on the Central Management Servers and select “Register Central Management Server”

3. Then register the SQL 2008 server.

4. Create the sub folders and register the required servers.

5. To run a query against all the servers, right click on the central management server and select “New Query”

6. In the Query editor, type your query and execute it.

Select SERVERPROPERTY('ProductVersion') AS 'Version',

SERVERPROPERTY('ProductLevel') AS 'Level',

SERVERPROPERTY('Edition') AS 'Edition'

7. In the results, you can notice that for each record the respective server name will be displayed.

This article is also available in pdf format for downloading.
Please Click here to get your copy now.

DBCC DBREINDEX in next versions

DBCC DBREINDEX rebuilds one or more indexes for a table in the specified database.
This was the command which we used to rebuild the indexes.
This feature will be removed in the next version (after 10.0 or SQL Server 2008) of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Alter INDEX command to reorganize or rebuild indexes instead.
Below query would reorganize the table if table fragementation is between 10.0 to 30.0 and Rebuild the index if table fragementation is above 30.0


use DB_name
go
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #Temp
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
DECLARE partitions CURSOR FOR SELECT * FROM #Temp

OPEN partitions

WHILE (1=1)

    BEGIN

        FETCH NEXT

           FROM partitions

           INTO @objectid, @indexid, @partitionnum, @frag;

        IF @@FETCH_STATUS < 0 BREAK;

        SELECT @objectname = QUOTENAME(o.name), @schemaname =QUOTENAME(s.name)

        FROM sys.objects AS o

        JOIN sys.schemas as s ON s.schema_id = o.schema_id

        WHERE o.object_id = @objectid;

        SELECT @indexname = QUOTENAME(name)

        FROM sys.indexes

        WHERE  object_id = @objectid AND index_id = @indexid;

        SELECT @partitioncount = count (*)

        FROM sys.partitions

        WHERE object_id = @objectid AND index_id = @indexid;

        IF @frag < 30.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REORGANIZE';

        IF @frag >= 30.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REBUILD';

        IF @partitioncount > 1

            SET @command = @command + N' PARTITION=' +CAST(@partitionnum AS nvarchar(10));

        EXEC (@command);

        PRINT N'Executed: ' + @command;

    END;

CLOSE partitions;

DEALLOCATE partitions;

DROP TABLE #Temp

GO

Note: Rebuilding a clustered index does not rebuild associated non-clustered indexes unless the keyword ALL is specified

When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction.

USE DB_name;

GO

ALTER INDEX ALL ON schema.table_name

REBUILD WITH (SORT_IN_TEMPDB = ON,

              STATISTICS_NORECOMPUTE = ON);

GO

This piece of information was shared to me by Kumaravyas. 

Thanks to Kumaravyas for sharing this useful info.

Search for a Column in all databases

Here is a Stored Procedure which scans all your databases for the Column which you are searching for.
It might be easy to find a Column in a server which has less databases, but it is a bit difficult when there are more number of databases and it is also time consuming to do it manually. So to avoid the manual job, use the below stored procedure FindColumn. This will search for the given Column in all databases and provides the result with the schema name, Table Name and the database name in which it is present.
This Code is tested for SQL Server 2005 and SQL server 2008.

Code:
Create Proc FindColumn 
@ColumnName nVarchar(50)
As
/*
Purpose       : Search a Column in all databases
Author : Sandesh Segu
Date            : 17th July 2009
Version : 1.0
More Scripts : http://sanssql.blogspot.com
*/

Create Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50),ColumnName varchar(50))

Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
insert into #temp 
Select ''?'' AS DatabaseName ,SS.Name as SchemaName ,ST.Name AS TableName ,SC.Name AS ColumnName from sys.tables ST ,sys.columns SC ,sys.schemas SS where SC.object_id=ST.object_id and ST.schema_id=SS.schema_id and SC.name like '''+@ColumnName+''''

EXEC sp_msforeachdb @SQL

Select * from #temp

Drop table #temp
GO

/* 
Usage: If the exact table name is known then specify the table name else include the wild cards
*/
EXEC FindColumn 'EmployeeID'
EXEC FindTable '%Employee%'

Search for a Table in all databases

Here is a Stored Procedure which scans all your databases for the table which you are searching for.
It might be easy to find a table in a server which has less databases, but it is a bit difficult when there are more number of databases and it is also time consuming to do it manually. So to avoid the manual job, use the below stored procedure FindTable. This will search for the given table in all databases and provides the result with the schema name and the database name in which it is present.
This Code is tested for SQL Server 2005 and SQL server 2008.

Code:
Create Proc FindTable
@TableName nVarchar(50)
As
/*
Purpose : Search for a Table in all databases
Author : Sandesh Segu
Date : 17th July 2009
Version : 1.0
More Scripts  : http://sanssql.blogspot.com
*/
Create Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50))

Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
if exists(Select name from sys.tables where name like '''+@TableName+''') 
insert into #temp 
Select ''?'' AS DatabaseName ,SS.Name AS SchemaName ,ST.Name AS TableName from sys.tables as ST , sys.schemas SS 
where ST.Schema_ID=SS.Schema_ID and ST.name like '''+@TableName+''''

EXEC sp_msforeachdb @SQL

Select * from #temp

Drop table #temp
GO

/* 
Usage: If the exact table name is known then specify the table name else include the wild cards
*/
EXEC FindTable 'Employee'
EXEC FindTable '%Employee%'

Record Count Utility

Hello Guys,

I have been working from past three days on developing a tool to get the record count of all the tabes in a particular database to an excel sheet directly.
Here is the tool that i have developed. It has got options to select the version of SQL server and get the count.

To download this utility click on the below links.

This tool is available in Excel 2003 format and Excel 2007 format also.

To Download this utility in Excel 2003 format, Click Here.

File Name: Get Record Count_V2.0_2003.xls

To Download this utility in Excel 2007 format, Click Here.

File Name: Get Record Count_V2.0_2003.xlsm

Test this utility and provide your feedback and suggestions for improvement.

Please send your feedbacks and suggestions to segu.sandesh@gmail.com