Category Archives: Scripts

How to find out how many objects of different types are there in a SQL database

Posted on by

I have sometimes found the need on data corruption cases to compare the number of objects exported to the destination database with the source database to find out which objects got exported and which didn’t. You would need to run this script against the source and destination database and compare the output. 

One of the drawbacks of the “Generate Scripts” option in SQL Server Management Studio is that SSMS scripts out the Primary Key constraints along with the CREATE TABLE script. So, if the table is already created, the primary key will not be created if you use the script that was generated by the SSMS Generate Scripts Wizard. 

Script 

select 

CASE xtype 

WHEN 'C' THEN 'CHECK constraint' 

WHEN 'D' THEN 'Default or DEFAULT constraint ' 

WHEN 'F' THEN 'FOREIGN KEY constraint ' 

WHEN 'L' THEN 'Log ' 

WHEN 'FN' THEN 'Scalar function ' 

WHEN 'IF' THEN 'In-lined table-function ' 

WHEN 'P' THEN 'Stored procedure ' 

WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K) ' 

WHEN 'RF' THEN 'Replication filter stored procedure' 

WHEN 'S' THEN 'System table ' 

WHEN 'TF' THEN 'Table function ' 

WHEN 'TR' THEN 'Trigger ' 

WHEN 'U' THEN 'User table ' 

WHEN 'UQ' THEN 'UNIQUE constraint (type is K) ' 

WHEN 'V' THEN 'View ' 

WHEN 'X' THEN 'Extended stored procedure' 

ELSE 'UNKNOWN' 

END, count(*) as counts 

from sys.sysobjects 

group by CASE xtype 

WHEN 'C' THEN 'CHECK constraint' 

WHEN 'D' THEN 'Default or DEFAULT constraint ' 

WHEN 'F' THEN 'FOREIGN KEY constraint ' 

WHEN 'L' THEN 'Log ' 

WHEN 'FN' THEN 'Scalar function ' 

WHEN 'IF' THEN 'In-lined table-function ' 

WHEN 'P' THEN 'Stored procedure ' 

WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K) ' 

WHEN 'RF' THEN 'Replication filter stored procedure' 

WHEN 'S' THEN 'System table ' 

WHEN 'TF' THEN 'Table function ' 

WHEN 'TR' THEN 'Trigger ' 

WHEN 'U' THEN 'User table ' 

WHEN 'UQ' THEN 'UNIQUE constraint (type is K) ' 

WHEN 'V' THEN 'View ' 

WHEN 'X' THEN 'Extended stored procedure' 

ELSE 'UNKNOWN' 

END

[Blog Reference]: Listing SQL instances in your environment

Posted on by

I have had multiple questions on how to enumerate SQL instances in your complete environment to run maintenance operations from a centralized environment. There are multiple tools which implement one of the methods or a variation of the methods described in the blog post below.

I keep referring people to this blog post. So, I thought I would put a referral of the same on my blog so that I can find it easily the next time.

Jonathan Sayce [Blog] has shown in his blog post what are the different methods to achieve this.

http://sqlblogcasts.com/blogs/jonsayce/archive/2008/02/10/programatically-listing-sql-servers.aspx

Monitoring Merge Agent performance using T-SQL

Posted on by

I have always loved writing T-SQL scripts and am ever-ready to write a T-SQL script when the opportunity presents itself. I just wrote another one for monitoring Merge Agent performance and spewing out some legible English to track the slow performing Merge Agents in your Replication Topology.

Read about it on:

http://blogs.msdn.com/repltalk/archive/2010/03/16/tracking-merge-agent-performance-using-distribution-database-system-tables.aspx

The script can be downloaded from either the above blog post or from the Script Center Gallery:

http://gallery.technet.microsoft.com/ScriptCenter/en-us/20467f5a-99f1-4313-9f12-6ac24346da14

How to change the TCP/IP port for a SQL Server instance

Posted on by

I wrote this script sometime back. I thought it would be a good idea to share this script. The background for writing this script was there were a bunch of SQL Server installations in the environment with the same instance names but on different boxes. The installations were pushed through programmatically and company policy was set in place which needed the TCP/IP port to be changed to a different port other than the default port i.e. 1433. The DBA obviously wouldn’t be too happy sitting and using SQL Server Configuration Manager on multiple servers doing the same monotonous task. The ask was to write a script which could be pushed to all the servers so that the port on which the SQL instance was listening on was modified programmatically.

This script changes the PORT number for a named instance of SQL Server 2008 called KATMAI to 1434. The namespace used here are:

SQL Server 2005: \root\Microsoft\SqlServer\ComputerManagement

SQL Server 2008: \root\Microsoft\SqlServer\ComputerManagement10

So if you wanted to use the script for SQL Server 2005, you would need to change the namespace to the one for SQL Server 2005. The other variables that come into play for this script are the Instance Name and the Port number:

‘Specify the instance name if any. Default is MSSQLSERVER.
‘Assumption is that we are running this for the default instance.
‘For a named instance, only provide the instance name. Eg: For a named instance LABDC\INST1, the parameter below would be strInstance = "INST1"
‘For a cluster, we need to run this script on the active node.

strInstance = "KATMAI"

‘Specify the new PORT

strPort = "1434"

So, you would have to modify the above variables for the correct Instance Name and Port number.

The script then makes use of the ServerNetworkProtocolProperty class methods to set the TCP port value.

If you need to extend this script, you can do so by creating an Array for the list of Servers in your environment and running the entire code in the script in another WHILE loop.

Another customized implementation could be if you want to change the port number for multiple instances with incremental values of Port Numbers. This can be done by querying the same namespace for getting the SQL instances on the box using SqlService. A sample WMI script is provided in the blog post below.

HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE

Once you have the Instance Names, then you can again create a loop with the Port Number variable being incremented and execute the function to set the TCP/IP Port number.

Download the script from here

Reference:

How to- Configure a Server to Listen on a Specific TCP Port

Happy Automation!! 🙂

Find FullText Search settings information for all catalogs & databases (in a jiffy)

Posted on by

Good morning folks – Ever worked on full text search in SQL Server and found it frustrating due to lack of information when anything goes bad & you are left poking around on what information to collect. Here is a script that you can use to collect Full-text (FTS) information for catalogs in ALL your databases. This will tell you the following,

  • Catalog Name & catalog ID
  • Item count
  • Population Status
  • Is Change Tracking enabled or not
  • Last Crawl – was it FULL, Incremental or UPDATE. Also gives you time of last crawl
  • Path of catalog storage location
EXEC sp_msforeachdb 'IF EXISTS (select * from ?.sys.fulltext_catalogs) BEGIN PRINT ''In Database: ?'' SELECT cat.name AS [CatalogName], cat.fulltext_catalog_id AS [CatalogID], FULLTEXTCATALOGPROPERTY(cat.name,''LogSize'') AS [ErrorLogSize], FULLTEXTCATALOGPROPERTY(cat.name,''IndexSize'') AS [FullTextIndexSize], FULLTEXTCATALOGPROPERTY(cat.name,''ItemCount'') AS [ItemCount], FULLTEXTCATALOGPROPERTY(cat.name,''UniqueKeyCount'') AS [UniqueKeyCount], FULLTEXTCATALOGPROPERTY(cat.name,''PopulateStatus'') AS [PopulationStatus], tbl.change_tracking_state_desc AS [ChangeTracking], tbl.crawl_type_desc AS [LastCrawlType], tbl.crawl_start_date AS [LastCrawlSTARTDate], tbl.crawl_end_date AS [LastCrawlENDDate], ISNULL(cat.path,N'''') AS [RootPath], CAST((select(casewhen exists(select distinct object_id from sys.fulltext_indexes fti where cat.fulltext_catalog_id = fti.fulltext_catalog_id and OBJECTPROPERTY(object_id, ''IsTable'')=1)then 1 else 0 end)) AS bit) AS [HasFullTextIndexedTables] FROM ?.sys.fulltext_catalogs AS catLEFT OUTER JOIN ?.sys.filegroups AS fg ON cat.data_space_id = fg.data_space_id LEFT OUTER JOIN ?.sys.database_principals AS dp ON cat.principal_id=dp.principal_idLEFT OUTER JOIN ?.sys.fulltext_indexes AS tbl ON cat.fulltext_catalog_id = tbl.fulltext_catalog_id PRINT '''' END'