17 October 2012

Script to get the details of SQL Server Installed Location , SQL Server Error Log location and Product Level for the Instance

                declare @RegPathParams sysname
                declare @Arg sysname
                declare @Param sysname
                declare @MasterPath nvarchar(512)
                declare @LogPath nvarchar(512)
                declare @ErrorLogPath nvarchar(512)
                declare @n int

                select @n=0
                select @RegPathParams=N'Software\Microsoft\MSSQLServer\MSSQLServer'+'\Parameters'
                select @Param='dummy'
                while(not @Param is null)
                    select @Param=null
                    select @Arg='SqlArg'+convert(nvarchar,@n)

                    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', @RegPathParams, @Arg, @Param OUTPUT
                    if(@Param like '-d%')
                        select @Param=substring(@Param, 3, 255)
                        select @MasterPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
                    else if(@Param like '-l%')
                        select @Param=substring(@Param, 3, 255)
                        select @LogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
                    else if(@Param like '-e%')
                        select @Param=substring(@Param, 3, 255)
                        select @ErrorLogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))

                    select @n=@n+1

                declare @SmoRoot nvarchar(512)
                exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT

CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName]

11 October 2012

ALL Constraints of a given Database

SELECT KCU.table_name,

KCU.column_name field_name,


CASE TC.is_deferrable WHEN 'NO' THEN 0 ELSE 1 END 'is_deferrable',

CASE TC.initially_deferred WHEN 'NO' THEN 0 ELSE 1 END 'is_deferred',

rc.match_option 'match_type',

rc.update_rule 'on_update',

rc.delete_rule 'on_delete',

ccu.table_name 'references_table',

ccu.column_name 'references_field',

KCU.ordinal_position 'field_position'



ON KCU.table_name =TC.table_name

AND KCU.table_schema =TC.table_schema

AND KCU.table_catalog =TC.table_catalog

AND KCU.constraint_catalog =TC.constraint_catalog

AND KCU.constraint_name =TC.constraint_name


ON rc.constraint_schema =TC.constraint_schema

AND rc.constraint_catalog =TC.constraint_catalog

AND rc.constraint_name =TC.constraint_name


ON rc.unique_constraint_schema = ccu.constraint_schema

AND rc.unique_constraint_catalog = ccu.constraint_catalog

AND rc.unique_constraint_name = ccu.constraint_name

WHERE KCU.constraint_catalog = DB_NAME()

ORDER BY KCU.constraint_name,


Source :- http://www.extradrm.com/blog/?p=376

09 October 2012

Rebuild Resource Database

Resolution  1:

Try to detach and attach the DB files from some other SQL Server with same version and patch level.
Bring SQL Server ONline.

Resolution 2:

The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.

To rebuild the resource system database:

  1. Launch the SQL Server 2012 Setup program (setup.exe) from the distribution media.
  2. In the left navigation area, click Maintenance, and then click Repair.
  3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. ClickOK or Install to continue.
  4. On the Select Instance page, select the instance to repair, and then click Next.
  5. The repair rules will run to validate the operation. To continue, click Next.
  6. From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.

    More on http://msdn.microsoft.com/en-us/library/dd207003.aspx#RebuildProcedure

06 October 2012

Contained Databases - SQL Serve 2012

What are the Benefits of using Contained Database ?
Contained databases can be easily moved to another server and start working instantly without the need of any additional configuration like adding user, mapping SID’s again. As Contained database have no external dependencies.
What type of Authentication Modes are supported by Contained Database?
Contained Databases supports
  • SQL Server Authentication
  • Windows Based Authentication
A contained user is a user without a login in the master database which resides in a Contained Database and can connect to this database specifying its credentials in the connection string. This user doesn't exists in master’s syslogins tables and will not even show up when you list all SQL Users.