Feeds:
Posts
Comments

Archive for the ‘Data Definition Language (DDL) Statements’ Category

SQL SERVER 2012 :

Given below are the methods you can use to change the compatibility level in SQL Server 2012.

Method 1 :

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 90

But you cannot change it to  COMPATIBILITY LEVEL 80 in SQL SERVER 2012.

Lets try to change the compatibility level  to 80 in SQL SERVER 2012.

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 80
--RESULT

Msg 15048, Level 16, State 1, Line 1
Valid values of the database compatibility level are 90, 100, or 110.

Version of SQL Server with respect to compatibility level.

  • SQL Server 2012 = COMPATIBILITY_LEVEL 110
  • SQL Server 2008 = COMPATIBILITY_LEVEL 100
  • SQL Server 2005 = COMPATIBILITY_LEVEL 90
  • SQL Server 2000 = COMPATIBILITY_LEVEL 80

Method 2 :

   EXEC sp_dbcmptlevel [AdventureWorks2012] , 110;

This method will not be supported from the future version of SQL SERVER. So, don’t use this in the future development.


SQL SERVER 2005 / 2008 :
Given below is the method to set the compatibility level in SQL SERVER 2005 and above.

   EXEC sp_dbcmptlevel [AdventureWorks] , 90;

Read Full Post »

Today, I was working with the trigger and I wanted to get the list of all triggers in the database. It is either enabled or disabled with its details. Finally I developed a script to get all the details in one shot.

For Example : If the trigger is for insert/update/delete or for any other operations, then the given below script will give you all the information related to triggers :

Create PROCEDURE [Get_All_Details_Of_Trigger]
as
Select A.[name] as [Table Name]
,B.[name] as [Trigger Name]
,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsTriggerDisabled') =1 
then 'DISABLED' else 'ENABLED' end) as [Trigger Status]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsInsertTrigger') =1 
then 'No' else 'Yes' end) as [IsInsertTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsFirstInsertTrigger') =1 
then 'No' else 'Yes' end) as [IsFirstInsertTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsLastInsertTrigger') =1 
then 'No' else 'Yes' end) as [IsLastInsertTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsDeleteTrigger') =1 
then 'No' else 'Yes' end) as [IsDeleteTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsFirstDeleteTrigger') =1 
then 'No' else 'Yes' end) as [IsFirstDeleteTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsLastDeleteTrigger') =1 
then 'No' else 'Yes' end) as [IsLastDeleteTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsUpdateTrigger') =1 
then 'No' else 'Yes' end) as [IsUpdateTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsFirstUpdateTrigger') =1 
then 'No' else 'Yes' end) as [IsFirstUpdateTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsLastUpdateTrigger') =1 
then 'No' else 'Yes' end) as [IsLastUpdateTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsAfterTrigger') =1 
then 'No' else 'Yes' end) as [IsAfterTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsInsteadOfTrigger') =1 
then 'No' else 'Yes' end) as [IsInsteadOfTrigger]

,(CASE WHEN OBJECTPROPERTY(B.[object_id], 'ExecIsTriggerNotForRepl') =1 
then 'No' else 'Yes' end) as [IsTriggerNotForReplication]

from sys.tables A 
Inner Join sys.triggers B 
On A.[object_id]=B.[parent_id]
GO
EXEC [Get_All_Details_Of_Trigger]

Read Full Post »