To get Foreign Key relations in the database

Hi Friends
below query is to get all the Foreign Keys and and to get relation between tables in the database

select
pk.TABLE_NAME ‘PK_Table’
, pk.COLUMN_NAME ‘PK_Column’
, fk.TABLE_NAME ‘FK_Table’
, fk.COLUMN_NAME ‘FK_Column’
, c.CONSTRAINT_NAME
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK on FK.CONSTRAINT_NAME = c.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK on PK.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
ORDER BY
1,2,3,4

Open SQL Server 2012 from Run command

Open SQL Server 2012 from Run command:

Daily I will open SQL Server Management Studio through SSMS command on run command. Yesterday SQL Server 2012 is installed on my machine. So SQL Server 2008 R2 and SQL Server 2012 both are installed on my machine. When i give ssms in run command, its opening SQL Server 2008 R2 only, To open SQL Server 2012 from the run command when both instances(SQL Server 2008 R2 and SQL Server 2012) are installed, we need to do below settings.

To confirm this, follow the following steps in Windows 7/8:

1. Go to Start -> Computer -> right-click and go to Properties
2. Go to “Advanced system settings”
3. In the “System Properties” window, go to the “Advanced” tab
4. Click on “environment variables”
5. Under system variables, look for the value of the PATH variable
6. Copy the path and paste it into Notepad. You will find both – paths for SQL 2008 (100) and for SQL 2012 (110). Here, swap the order of the SQL Server related paths
7. Update the path variable, click OK and you will be all set

NOTE: Before you go about doing this, please make sure you are comfortable with editing system variables and PATH values. An error here may cause the server/workstation to stop functioning. If you have the slightest doubts, please have a systems/network administrator from your IT team do it for you.

Default Compatability Level of SQL 2012

Hi friends,

Below are the Default COMPATIBILITY LEVEL’s of SQL Server Versions.

Default SQL Server 2005 COMPATIBILITY_LEVEL = 90

Default SQL Server 2008 and SQL Server 2008 R2 COMPATIBILITY_LEVEL = 100

Default SQL Server 2012 COMPATIBILITY_LEVEL = 110

To change the COMPATIBILITY_LEVEL of the database.

ALTER DATABASE AdventureWorks2012
SET COMPATIBILITY_LEVEL = 110;

Reserved Keywords

The compatibility setting also determines the keywords that are reserved by the Database Engine. The following table shows the reserved keywords that are introduced by each of the compatibility levels.


Reserved keywords

110 – WITHIN GROUP, TRY_CONVERT, SEMANTICKEYPHRASETABLE, SEMANTICSIMILARITYDETAILSTABLE, SEMANTICSIMILARITYTABLE

100 – CUBE, MERGE, ROLLUP

90 – EXTERNAL, PIVOT, UNPIVOT, REVERT, TABLESAMPLE

Giving Read and Execute permissions to the Database

Giving Read and Execute permissions to the Database

USE YourDatabaseHere
GO

– This is to get read permissions to the user
EXEC sp_addrolemember ‘db_datareader’, ‘derekdieter’

– For Creating new role
CREATE ROLE ReadExecute

use YourDatabaseHere
GO

– To give selected permissions to the role.
GRANT EXECUTE, Select, VIEW Definition ON SCHEMA::[dbo] TO [ReadExecute]

GO

USE YourDatabaseHere

GO
– For assign ReadExecute role permissions to User
EXEC sp_addrolemember ‘ReadExecute’, ‘derekdieter’

GO

– To assing ReadExecute role permissions to User group
EXEC sp_addrolemember ‘ReadExecute’, ‘DeveloperGroup’

– To give selected permissions to the user directly (With out creating role).
GRANT EXECUTE, Select, VIEW Definition ON SCHEMA::[dbo] TO [Active Directory User/ SQL Server User]