September 2009 | SansSQL

Wednesday, September 30, 2009

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%'

Saturday, September 26, 2009

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

Thursday, September 17, 2009

What is Statistics in SQL Server

Statistics in SQL Server refers specifically to information that the server collects about the distribution of data in columns and indexes. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. Good statistics allow the optimizer to accurately assess the cost of different query plans, and choose a high-quality plan.
This feature is Set to ON by default in SQL Server 2000 onwards i.e. in SQL Server 2000, SQL Server 2005 and SQL Server 2008.

The automatic statistics creation function can be disabled at the database level by executing
ALTER DATABASE DBName SET AUTO_CREATE_STATISTICS OFF

Create Statistics:
Syntax:
CREATE STATISTICS statistics_name
ON { table | view } ( column [ ,...n ] )
         [ WITH
                 [  [ FULLSCAN
                       | SAMPLE number { PERCENT | ROWS } ] [ , ] ]
                 [ NORECOMPUTE ]
         ]

Example:
This example creates the City statistics group (collection), which calculates random sampling statistics on
five percent of the AddressLine1 and City columns of the Address table.
Use AdventureWorks
Go
CREATE STATISTICS City
ON Person.Address (AddressLine1, City)
WITH SAMPLE 5 PERCENT
Go

Drop Statistics:
Syntax:
DROP STATISTICS table.statistics_name | view.statistics_name
Example:
Use AdventureWorks
Go
DROP STATISTICS Person.Address.City
 
Update Statistics:
Syntax:
UPDATE STATISTICS table | view
           [
                   index
                   | ( statistics_name [ ,...n ] )
           ]
           [        WITH
                          [
                               [ FULLSCAN ]
                               | SAMPLE number { PERCENT | ROWS } ]
                               |RESAMPLE
                          ]
                          [ [ , ] [ ALL | COLUMNS | INDEX ]
                          [ [ , ] NORECOMPUTE ]
           ]

Example:
Use AdventureWorks
Go
UPDATE STATISTICS Person.Address(City)
       WITH FULLSCAN, NORECOMPUTE
Go
 
sp_updatestats:
Runs UPDATE STATISTICS against all user-defined tables in the current database.
 
Example:
Use AdventureWorks
Go
EXEC sp_updatestats
 
sp_autostats:

Displays or changes the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.
Syntax:

Exec sp_autostats [ @tblname = ] 'table_name'
                [ , [ @flagc = ] 'stats_flag' ]
                [ , [ @indname = ] 'index_name' ]
Example:
This example displays the current statistics status of all indexes on the authors table.
USE AdventureWorks
Go
EXEC sp_autostats Person.Address
This example enables the automatic statistics setting for all indexes of the authors table.
USE AdventureWorks
Go
EXEC sp_autostats Person.Address, 'ON'

DBCC SHOW_Statistics:
Displays the current distribution statistics for the specified target on the specified table.

Syntax:
DBCC SHOW_STATISTICS ( table , target )
 
Example:
USE AdventureWorks
Go
DBCC SHOW_STATISTICS (Person.Address, City)
Go

Refrences: Books Online

Sunday, September 6, 2009

Find Statistics Info for all tables

Here is the code to find Statistics info (if exists) of all the tables in a database.
This code is tested for SQL 2005 and SQL 2008.

If exists (Select * from sys.tables where name='StatsInfo')
Drop Table StatsInfo

Create Table StatsInfo
(TableName varchar(500),
ColumnName varchar(500),
StatsName varchar(500))

Exec sp_msforeachtable 'insert into StatsInfo (StatsName,ColumnName)
Exec sp_helpstats ''?'';
Update StatsInfo set TableName=''?'' where TableName is NULL'

Select * from StatsInfo

Thursday, September 3, 2009

Find row count of all the tables in a Database

Here is a quick and easy way to find the row count of all the tables in a database.
Before getting the row count, you have to update the usage of your database.

/*This Query works for SQL 2000, SQL 2005 and SQL 2008*/

DBCC UPDATEUSAGE('Your DB Name') WITH COUNT_ROWS
GO
Select object_name(id) as [Table Name],rowcnt as [RowCount] from sysindexes
where indid < 2 AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
Order by object_name(id)

/*This Query works for SQL 2005 and SQL 2008 as it uses DMV*/

DBCC UPDATEUSAGE('Your DB Name') WITH COUNT_ROWS
GO
Select Distinct Object_Name(object_id) as [Table Name], row_count as [RowCount] from sys.dm_db_partition_stats where OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
Order by Object_Name(object_id)

Ads