'TSQL'에 해당되는 글 5건
- 2010.06.04 데이터베이스 사이즈
- 2010.06.03 T-SQL::DB_Restore_move_to
- 2010.06.03 T-SQL::Convert hex value to String 32bit
- 2010.06.03 T_SQL::CONSTRAINT조사 1
- 2010.06.03 T_SQL::SP_WHO2 + DBCC
데이터베이스 사이즈 측정
- DB파일사이즈
- Estimating the size of Table
-
(Heap 사이즈 혹은 클러스터 인덱스 사이즈) + 넌 클러스터 인덱스 사이즈의 합
-
사이즈 계산법으로 말고 sp_spaceused 에 datasize와 인덱스 사이즈를 보고 테이블 전체 사이즈를 아는것과 다른가?
-
sp_spaceused 프로시저 확인해 보기 : 확인해 보니 복잡하지 않고 윗 부분과 동일한 것 같음
- --=====================================================
- - 테이블 사이즈
-
--======================================================
-
SELECT
-
@reservedpages = SUM (reserved_page_count),
-
@usedpages = SUM (used_page_count),
-
@pages = SUM (
-
CASE
-
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
-
ELSE lob_used_page_count + row_overflow_used_page_count
-
END
-
),
-
@rowCount = SUM (
-
CASE
-
WHEN (index_id < 2) THEN row_count
-
ELSE 0
-
END
-
)
-
FROM sys.dm_db_partition_stats
-
WHERE object_id = @id;
-
-
SELECT
-
name = OBJECT_NAME (@id),
-
rows = convert (char(11), @rowCount),
-
reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
-
data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
-
index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
-
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
-
-
이 글은 스프링노트에서 작성되었습니다.
'T-SQL' 카테고리의 다른 글
| Index::Defrag Script v4.0 (0) | 2010.06.15 |
|---|---|
| T_SQL::미 사용 Table (0) | 2010.06.15 |
| T-SQL::Removing Duplication Data (1) | 2010.06.03 |
| T-SQL::DB_Restore_move_to (0) | 2010.06.03 |
- --=============================================================
- -- 장비 이동하거나 DB 복원시 파일을 다른 디렉토리로 보관해야할때
- -- 파일들이 많은 DB의 경우 일일이 변경하기 어려움이 있어서 원본 기준으로 복원스크립트 생성하고
- -- 디렉토리 경로와 백업 파일 경로만 넣어주면 되게끔 생성 한다.
- --==============================================================
-
-
SET NOCOUNT ON
-
DECLARE @sql nvarchar(max)
-
DECLARE @sql_move nvarchar(3000)
-
DECLARE @move nvarchar(200)
-
DECLARE @backup_type char(1)
-
DECLARE @name sysname
-
SET @backup_type = 'L' --N
-
SET @sql_move = ''
-
DECLARE cur_restore CURSOR FOR
-
SELECT name FROM sys.databases WHERE database_id > 4 and NAME != 'LiteSpeedLocal' and state = 0
-
ORDER BY name
-
-
OPEN cur_restore
-
FETCH NEXT FROM cur_restore
-
INTO @name
-
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
SET @sql = NULL
SET @sql_move = '' -
IF @backup_type = 'L'
-
BEGIN
-
SET @sql = 'exec master.dbo.xp_restore_database' + char(13)
-
+ '@database = ''' + @name + '''' + char(13)
-
+ ',@filename = ''''' + char(13)
-
+ ',@filenumber = 1' + char(13)
-
+ ',@with = ''REPLACE''' + char(13)
-
+ ',@with = ''NORECOVERY''' + char(13)
-
-
-
DECLARE cur_move CURSOR FOR
-
SELECT ',@with = ''MOVE ''''' +
-
name + ''''' TO N''''' +
-
filename + ''''''' '
-
FROM sys.sysaltfiles WHERE dbid = db_id(@name)
-
ORDER BY fileid
-
OPEN cur_move
-
FETCH NEXT FROM cur_move
-
INTO @move
-
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
SET @sql_move = @sql_move + @move + char(13)
-
-
FETCH NEXT FROM cur_move
-
INTO @move
-
END
-
CLOSE cur_move
-
DEALLOCATE cur_move
-
-
-
END
-
ELSE IF @backup_type = 'N'
-
BEGIN
-
SET @sql = 'RESTORE DATABASE ' + @name + char(13)
-
+ 'FROM DISK =''''' + char(13)
-
+ 'WITH NORECOVERY' + char(13)
-
-
DECLARE cur_move CURSOR FOR
-
SELECT ',MOVE ''' + name + ''' TO ''' + filename + ''''
-
FROM sys.sysaltfiles WHERE dbid = db_id(@name)
-
ORDER BY fileid
-
OPEN cur_move
-
FETCH NEXT FROM cur_move
-
INTO @move
-
-
WHILE @@FETCH_STATUS = 0
-
BEGIN
-
SET @sql_move = @sql_move + @move + char(13)
-
-
FETCH NEXT FROM cur_move
-
INTO @move
-
END
-
CLOSE cur_move
-
DEALLOCATE cur_move
-
END
-
-
SET @sql = @sql + @sql_move + char(13)
-
-
print @sql
-
-
FETCH NEXT FROM cur_restore
-
INTO @name
-
END
-
-
CLOSE cur_restore
-
DEALLOCATE cur_restore
이 글은 스프링노트에서 작성되었습니다.
'T-SQL' 카테고리의 다른 글
| 데이터베이스 사이즈 (0) | 2010.06.04 |
|---|---|
| T-SQL::Removing Duplication Data (1) | 2010.06.03 |
| T-SQL::Convert hex value to String 32bit (0) | 2010.06.03 |
| T_SQL::CONSTRAINT조사 (1) | 2010.06.03 |
Convert Hex value to Signed 32-bit int
- /* ======================================================================== *
* Function Name: fnHexToInt *
* *
* -- Description --------------------------------------------------------- *
* Convert, up to 8 nibbles, hex string ('FFFFFFFF'...'00000000') *
* to *
* Signed 32-bit int (-2147483648..2147483647). *
* -- History ------------------------------------------------------------- *
* 1.0.0 28.Oct.2001, Ofer Bester *
* ======================================================================== */
PRINT '* CREATE FUNCTION: fnHexToInt, Ver. 1.0.0 (28.Oct.2001).'
SETUSER 'dbo'
go - IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToInt' AND type IN ('FN', 'IF', 'TF'))
BEGIN
PRINT ' + DROP FUNCTION dbo.fnHexToInt.'
DROP FUNCTION dbo.fnHexToInt
END
PRINT ' + CREATE FUNCTION dbo.fnHexToInt.'
go - CREATE FUNCTION dbo.fnHexToInt (@Hex varchar(8))
RETURNS int
-- Ver. 1.0.0 28.Oct.2001, Ofer Bester
AS BEGIN
DECLARE @i tinyint,
@Nibble tinyint,
@ch char(1),
@Result int - SET @i = 1 -- Init nibble counter
SET @Result = 0 -- Init output parameter - SET @Hex = UPPER( LTRIM( RTRIM( @Hex ) ) ) -- Convert to uppercase
- WHILE (@i <= LEN(@Hex))
BEGIN
SET @ch = SUBSTRING(@Hex, @i, 1) - IF (@ch >= '0' AND @ch <= '9') SET @Nibble = ASCII(@ch) - ASCII('0')
ELSE IF (@ch >= 'A' AND @ch <= 'F') SET @Nibble = ASCII(@ch) - ASCII('A') +10
ELSE RETURN NULL - IF( @Result > 0x7FFFFFF) -- 134217727 = 0x7FFFFFF
BEGIN
SET @Result = @Result & 0x7FFFFFF -- Set MSB, of 7 nibbles, OFF
SET @Result = @Result * 16 + @Nibble +0x80000000 -- Shift left 4Bits, Add last nibble and convert to negetive number.
END
ELSE BEGIN
SET @Result = @Result *16 +@Nibble -- Shift left 4Bits, Add nibble.
END - SET @i = @i +1 -- Next nibble.
END -- While - RETURN ( @Result )
END -- Function
go - -- Check create result -------------------------------------------------------
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToInt' AND type IN ('FN', 'IF', 'TF'))
BEGIN
PRINT 'DONE (CREATE FUNCTION: fnHexToInt).'
END
ELSE BEGIN
PRINT '****** FAILES (CREATE FUNCTION: fnHexToInt) ******'
END
PRINT ''
go - SETUSER
go
이 글은 스프링노트에서 작성되었습니다.
'T-SQL' 카테고리의 다른 글
| T-SQL::Removing Duplication Data (1) | 2010.06.03 |
|---|---|
| T-SQL::DB_Restore_move_to (0) | 2010.06.03 |
| T_SQL::CONSTRAINT조사 (1) | 2010.06.03 |
| SQL Server 2005 and 2008 Ranking Functions (0) | 2010.04.05 |
CONSTRAINT 조사
- select * from sys.default_constraints
- select * from sys.key_constraints
- select * from sys.check_constraints
- --=================
- -- 2005용
- --=================
- -- FK
- SELECT object_name(fk.constraint_object_id) AS fk_name,
- fk.constraint_column_id AS fk_clolum,
- object_name(fk.parent_object_id) AS parent_name,
- (select name from sys.columns where object_id = fk.parent_object_id and column_id = fk.parent_column_id) as parent_column,
- object_name(fk.referenced_object_id) AS referenced_name,
- (select name from sys.columns where object_id = fk.referenced_object_id and column_id = fk.referenced_column_id) as referenced_column
- FROM sys.foreign_key_columns as fk
- WHERE fk.parent_object_id = object_id('<@table_name, @sysname,@table_name>')
- or fk.referenced_object_id = object_id('<@table_name, @sysname,@table_name>')
- -- DEFAULT
- SELECT object_name(parent_object_id) as table_name,
- name,
- (select name from sys.columns where object_id = df.parent_object_id and column_id = df.parent_column_id) as column_name,
- definition
- FROM sys.default_constraints as df
- WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
- -- DEFAULT
- SELECT
- object_name(parent_object_id) as table_name,
- name,
- (select name from sys.columns where object_id = ck.parent_object_id and column_id = ck.parent_column_id) as column_name,
- definition
- FROM sys.check_constraints as ck
- WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
- --=================
- -- 2000용
- --=================
- --FK
- select object_name(fkeyid) , object_name(constid),object_name(rkeyid)
- from sys.sysforeignkeys where fkeyid = object_id ('<@table_name, @sysname,@table_name>')
- select object_name(id), object_name(constid) ,status from sys.sysconstraints
- where status&5 = 1 --FK
- where constid = object_id('<@table_name, @sysname,@table_name>')
- order by object_name(constid)
- --===========================
- -- DROP
- --===========================
- -- FK 2005용
- SELECT 'ALTER TABLE ' + bject_name(fk.parent_object_id) + ' DROP CONSTRAINT ' + object_name(fk.constraint_object_id)
- FROM sys.foreign_key_columns
- WHERE fk.parent_object_id = object_id('<@table_name, @sysname,@table_name>')
- -- DEFULT 2005용
- SELECT 'ALTER TBLE ' + object_name(parent_object_id) + ' DROP CONSTRAINT ' + name
- FROM sys.default_constraints
- WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
- -- CK 2005용
- -- DEFULT 2005용
- SELECT 'ALTER TBLE ' + object_name(parent_object_id) + ' DROP CONSTRAINT ' + name
- FROM sys.default_constraints
- WHERE object_name(parent_object_id) = object_id('<@table_name, @sysname,@table_name>')
- -- FK 2000용
- SELECT 'ALTER TABLE ' + object_name(fkeyid) + ' DROP CONSTRAINT ' + object_name(constid)
- FROM sys.sysforeignkeys
- WHERE fkeyid = object_id ('<@table_name, @sysname,@table_name>')
- -- INDEX 2000용
- select 'ALTER TABLE ' + object_name(id) + ' DROP INDEX ' + name
- from sys.sysindexes where object_name(id) in ('GOODSDAQ_BANNER_POSITION')
- order by id, name
이 글은 스프링노트에서 작성되었습니다.
'T-SQL' 카테고리의 다른 글
| T-SQL::DB_Restore_move_to (0) | 2010.06.03 |
|---|---|
| T-SQL::Convert hex value to String 32bit (0) | 2010.06.03 |
| SQL Server 2005 and 2008 Ranking Functions (0) | 2010.04.05 |
| DMV::Index (0) | 2010.04.05 |
-
--===================================
-
-- SP_WHO2와명령어동시확인하기
-
--===================================
-
SELECT
-
ISNULL(D.text, '') AS SQLStatement,
-
object_name(D.objectid) AS 'sp_ame',
-
A.Session_ID SPID,
-
ISNULL(B.status,A.status) AS [Status],
-
A.login_name AS [Login],
-
ISNULL(A.host_name, ' .') AS HostName,
-
ISNULL(CAST(C.BlkBy AS varchar(10)), ' .') AS BlkBy,
-
DB_NAME(B.Database_ID) AS DBName,
-
B.command,
-
ISNULL(B.cpu_time, A.cpu_time) AS CPUTime,
-
ISNULL((B.reads + B.writes),(A.reads + A.writes)) AS DiskIO,
-
A.last_request_start_time AS LastBatch,
-
ISNULL(A.program_name, '') AS ProgramName,
-
ISNULL(A.client_interface_name, '') AS ClientInterfaceName
-
FROM sys.dm_exec_sessions A
-
LEFT OUTER JOIN sys.dm_exec_requests B ON A.session_id = B.session_id
-
LEFT OUTER JOIN (
-
SELECT A.request_session_id SPID, B.blocking_session_id BlkBy
-
FROM sys.dm_tran_locks A
-
INNER JOIN sys.dm_os_waiting_tasks B ON A.lock_owner_address = B.resource_address
-
) C ON A.Session_ID = C.SPID
-
OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) D
-
go
이 글은 스프링노트에서 작성되었습니다.
'Monitoring' 카테고리의 다른 글
| T-SQL:: Default Trace로 DB 증가량 확인 (1) | 2011.04.15 |
|---|---|
| Admin::Tempdb 의 작업, 모니터링 (0) | 2011.01.30 |
| CPU에 할당된 Task 보기 (1) | 2010.03.14 |
| DBCC FLUSHPROCINDB (0) | 2010.02.04 |
Prev
