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

Thanks to :- http://www.sqlserver-training.com

                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)
                begin
                    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%')
                    begin
                        select @Param=substring(@Param, 3, 255)
                        select @MasterPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
                    end
                    else if(@Param like '-l%')
                    begin
                        select @Param=substring(@Param, 3, 255)
                        select @LogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
                    end
                    else if(@Param like '-e%')
                    begin
                        select @Param=substring(@Param, 3, 255)
                        select @ErrorLogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
                    end

                    select @n=@n+1
                end

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

SELECT
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]

ALL Constraints of a given Database

SELECT KCU.table_name,

KCU.column_name field_name,

TC.constraint_type,

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'

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

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

LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc

ON rc.constraint_schema =TC.constraint_schema

AND rc.constraint_catalog =TC.constraint_catalog

AND rc.constraint_name =TC.constraint_name

LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu

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,

KCU.ordinal_position;


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

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:

Rebuild the resource Database

---

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
   
   

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.

Contained DB Users

http://blogs.extremeexperts.com/2012/10/03/contained-databases-inside-sql-server-2012/