Category: MSSQL

Database Transfer MsSql server Sync sid . 데이타베이스 이전시에 아이디 싱크

ALTER Procedure [dbo].[usp_SyncLoginSid](@_LoginID varchar(30), @_DBName varchar(30))

AS

Begin

declare @_LoginID varchar(30)

declare @_DBName varchar(30)

set @_LoginID = ‘LoginID’

set @_DBName = ‘DatabaseName’

declare @vBinSid varbinary(85)

select @vBinSid = sid

From felis.dbo.sysusers where name = @_LoginID

exec sp_addlogin @_LoginID,’ ‘, @_DBName, @sid= @vBinSid

End

in ms sql 2008

without master db

—————how to create new login and sync

use DatabaseName

declare @sid varvanary

select @sid=sid from sysusers where name=’DatabaseName’

create login LoginID with password=’LoginID’, SID=@sid, check_policy=OFF

use master
GO

select
d.name,
d.dbid,
spid,
login_time,
nt_domain,
nt_username,
loginame
from sysprocesses p
inner join sysdatabases d
on p.dbid = d.dbid
where d.name = ‘database_name’
GO

kill 56 => kill the number in spid field
GO

exec sp_dboption ‘database_name’, ‘single user’, ‘FALSE’
GO

mssql server 2005 paging

SET @RowStart = @PageSize * (@Page-1) + 1;
SET @RowEnd = @RowStart + @PageSize – 1 ;

SELECT * FROM (

SELECT RowNumber() OVER(order by USER_ID) As RowID,

COUNT (USER_ID) OVER (PARTITION BY null) AS TOTAL_ROWS,

name

from usertbl

) As RowResults

WHERE RowID Between @RowStart AND @RowEnd

Order by RowID asc

mssql lock

http://blog.naver.com/PostView.nhn?blogId=tarius74&logNo=220272058523

테이블 정의서 2008 @@

select
case row_number() over(partition by o.name order by  o.name, c.column_id)
when 1 then o.name else ” end “Table Name”
,c.name “Column Name”
,case t.name
when ‘varchar’ then t.name +'(‘+cast(c.max_length as varchar) +’)’
when ‘char’ then t.name +'(‘+cast(c.max_length as varchar) +’)’
when ‘datetime’ then t.name
else t.name+'(‘+cast(c.precision as varchar)+’,’+cast(c.scale as varchar)+’)’ end
as “Column Type”
,case c.is_nullable when 0 then ‘NOT NULL’ else ” end “Nullable”
,isnull(d.definition,”) “Column Default”
,case when k.index_id is null then ” else ‘YES’ end “PK”
,e.value “Column Comment”
from sys.tables o
join sys.columns c on(o.object_id = c.object_id and o.type=’U’)
join sys.types t on(c.system_type_id = t.system_type_id and t.schema_id=4)
left outer join sys.extended_properties e on(c.object_id = e.major_id and c.column_id = e.minor_id)
left outer join sys.index_columns k on(c.object_id = k.object_id and c.column_id = k.column_id and index_id=1)
left outer join sys.default_constraints d on(c.object_id=d.parent_object_id and c.column_id=d.parent_column_id and d.type=’D’)
where o.name <> ‘sysdiagrams’
order by  o.name, c.column_id
;

인덱스 정의서 2008

SELECT
CASE table_RN WHEN 1 THEN A.table_name ELSE ” END table_name,
CASE RN WHEN 1 THEN A.index_name ELSE ” END index_name,
index_column_id,
colum_name,
is_unique,
is_primary_key
FROM
(

select
ROW_NUMBER() OVER(ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) sort_RN,
ROW_NUMBER() OVER(PARTITION BY o.name ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) table_RN,
ROW_NUMBER() OVER(PARTITION BY o.name, i.name ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) RN,
o.name table_name,
i.name index_name,
b.name colum_name,
a.index_column_id,
case i.is_unique when 1 then ‘YES’ else ” end is_unique,
case i.is_primary_key when 1 then ‘YES’ else ” end is_primary_key,
i.is_primary_key is_primary_key_idx
from
sys.all_objects o join
sys.indexes i on(o.object_id=i.object_id) join
sys.index_columns a on(i.object_id=a.object_id and i.index_id = a.index_id)
join sys.columns b on
(a.object_id = b.object_id and a.column_id = b.column_id)
where o.type=’U’
and i.index_id > 0
and o.name != ‘sysdiagrams’
) A
order by sort_RN

exerd 모델링

http://blog.naver.com/PostView.nhn?blogId=rmflt&logNo=110181374443

데이타 모델링의 이해

http://www.dbguide.net/db.db?cmd=view&boardUid=148182&boardConfigUid=9&categoryUid=216&boardIdx=132&boardStep=1

Download sqljdbc4-2.0.jar

[MSSQL]2008 백업 파일을 2005에서 복원하기

http://saddev.tistory.com/18

http://www.sqlusa.com/bestpractices2008/

​SQL 인젝션 공격을 당했을 경우 인젝션 스크립트 지우는 저장 프로시저