below query is to get all the Foreign Keys and and to get relation between tables in the database
, pk.COLUMN_NAME ‘PK_Column’
, fk.TABLE_NAME ‘FK_Table’
, fk.COLUMN_NAME ‘FK_Column’
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
To find the tables, which does not have identity property yes
where TABLE_NAME not in (
and table_type = ‘BASE TABLE’
Just follow the below link, when you forgot the SA password in SQL Server.
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.
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;
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.
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
– This is to get read permissions to the user
EXEC sp_addrolemember ‘db_datareader’, ‘derekdieter’
– For Creating new role
CREATE ROLE ReadExecute
– To give selected permissions to the role.
GRANT EXECUTE, Select, VIEW Definition ON SCHEMA::[dbo] TO [ReadExecute]
– For assign ReadExecute role permissions to User
EXEC sp_addrolemember ‘ReadExecute’, ‘derekdieter’
– 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]