0
Monthly Archives

October 2015

SQL

Shadow Database in DBMS

create database ShadowDB GO Use ShadowDB create table DemoTable(id int,name char(100)) create clustered index IDx_DemoTable on DemoTable(id) declare @initialVar int =0 while @initialVar<100 begin insert into…

Read More
SQL

Function returning Tables in SQL

Create Table DeptmentMaster(DID  int PRIMARY KEY identity(1,1),Dname varchar(25),Lname varchar(25))GO Create Table EmMaster(EID int identity(1,1),DID int FOREIGN KEY REFERENCES DeptmentMaster(DID),Fname varchar(25),Lname varchar(25)) GO INSERT INTO DeptmentMaster VALUES(‘ID’,’Solution’)INSERT…

Read More
SQL

Database snapshot in SQL Server

use demostart go exec sp_helpfile –creating snapshot database create database demostart_Snapshot on(name=demostart_Data,filename=’C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLdemostart_Snapshot.snap’) as snapshot of demostart — Reverting the soure database using snapshot…

Read More
SQL

CTE in SQL

USE demostartGO IF OBJECT_ID(‘Employees’, ‘U’) IS NOT NULLDROP TABLE dbo.EmployeesGOCREATE TABLE dbo.Employees(  EmployeeID int NOT NULL PRIMARY KEY,  FirstName varchar(50) NOT NULL,  LastName varchar(50) NOT NULL, …

Read More
SQL

Cross database Chaining

  –Enabling cross database ownership chaining at server  level sp_configure ‘cross db ownership chaining’,1 GO reconfigure GO select * from sys.configurations GO –Enabling cross database ownership…

Read More
SQL

Check Permissions in SQL

SELECT [UserName] = ulogin.[name], [UserType] = CASE princ.[type] WHEN ‘S’ THEN ‘SQL User’ WHEN ‘U’ THEN ‘Windows User’ WHEN ‘G’ THEN ‘Windows Group’ END, [DatabaseUserName] =…

Read More