• Click to subscribe to this blog

  Enter your email address to subscribe to this blog and receive notifications of new posts by email.

  Join 174 other followers

• Blog Readers

  • 606,980 Readers

• ---

  
  

  ---

  

  Grab this badge here!

• Disclaimer

  This is a personal blog. The opinions expressed here represent my own thoughts and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet/ BOL. My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have written my personal experience on this blog. Please don't direct implement or execute any query on production before have detailed study.

• Database Support

  • Notes (365)
    • Database (73)
    • DB Articles (34)
    • IIS (1)
    • Microsoft Technologies (1)
    • MySQL (11)
    • Networking (7)
    • Oracle (4)
    • OS and SQL (17)
    • SQL Scripts (36)
    • SQL Server (270)
    • SQL Server 2008 (142)
    • SQL Server 2008 R2 (136)
    • SQL Server 2011 (Denali) (46)
    • Windows OS (4)

• Top Rated

• Follow Me

  

  NetworkedBlogs

  Blog:

  SQLDBPOOL.COM

  Topics:

  Sql Server, Dba, Developer

  Follow my blog

• About Me

  Jugal Shah is the author of this blog. He has eight plus years of experience as Database Administrator and Developer in the Microsoft SQL Server and MySQL.

  

• Contact Me

  ---

  Email
  jugal.shah@sqldbpool.com
  Chat
  jshah.sqldbpool@gmail.com

  ---

  

  

• Twitter

  • Steps to change logged in account password on Remote Desktop Session Windows Server 2008/R2 box wp.me/pdaJF-ty 3 days ago
  • Setting the PowerShell Execution Policy wp.me/pdaJF-ts 4 days ago
  • How to make SQL Server View Read Only? wp.me/pdaJF-tq 5 days ago
  Follow @imjugal

• SQLDBPool Facebook Page

Archive for the ‘SQL Server 2008 R2’ Category

--creating a sample table
Create table tbl1
(
	myID int,
	name varchar(10)
)

--inserting data
insert into tbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')

--creating sample view
create view vwtbl1
as
select * from tbl1

--inserting data using view
insert into vwtbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')

--altering view to make it readOnly
alter view vwtbl1
as
select myid,name from tbl1
union all
select 0,0 where 1 =0

create table employee
(
	empID int not null,
	SSN int not null,
	name varchar(20)
)


ALTER TABLE [employee]
ADD CONSTRAINT pk_employee PRIMARY KEY (empID, SSN)


create table EmpDetail
(
		empID int,
		SSN int,
		address varchar(20),
		city varchar(20),
		pin varchar(20)
)

ALTER TABLE dbo.empDetail
   ADD CONSTRAINT FK_Employee
   FOREIGN KEY(empID, SSN)
   REFERENCES dbo.employee(empID, SSN)


SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME, 
    ccu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
      ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME IN ('employee','employeeDetail')

-- Adding the NON NULL constraint
ALTER TABLE [TableName]	 
ALTER COLUMN PK_ColumnName int NOT NULL

--Script to add the primary key on the existing table
ALTER TABLE [TableName]
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (PK_ColumnName)

--script to create non-clustered Index
create index IX_ColumName on TableName(ColumnName)
--script to create Unique constraint on the existing table
ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(ColumnName)

create database SQLDB collate Latin1_General_CS_AS;
go
create database SQLDB1 collate Latin1_General_CI_AS;
go

SELECT DATABASEPROPERTYEX('SQLDB', 'Collation') SQLDB;
SELECT DATABASEPROPERTYEX('SQLDB1', 'Collation') SQLDB1;

select *
from sys.server_principals
where name = 'sa' or substring(name, 1, 1) = '#' 

select 'DROP LOGIN ' + [name]
from sys.server_principals
where type_desc = 'SQL_LOGIN'
and sid <> 0x01 and substring(name, 1, 1) <> '#'

SELECT 
j.name,
l.[name] as [JobOwner]
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

EXEC MSDB.dbo.sp_update_job 
@job_name = 'Job Name', 
@owner_login_name = 'sa'

use sqlDBPool
--documents table will store files into varbinary field
--drop table documents
create table documents
(
	documentID int identity(1,1),
	doctype char(5),
	document varbinary(max)
)


--script to store/save document into table
insert into documents 
 Select 'xls', (SELECT * FROM OPENROWSET(BULK N'C:\JSpace\book1.xls', SINGLE_BLOB) AS document) document
go

select * from documents