World Search

Wednesday, October 17, 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)
                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]

Thursday, October 11, 2012

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

Tuesday, October 9, 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

Saturday, October 6, 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.