특정 컬럼명에 대한 테이블 및 속성 검색하는 쿼리 SQL

select table_name ,column_name ,column_default, data_type
	, character_maximum_length ,character_octet_length 
From information_schema .columns 
where column_name='cityCd' 
order by character_maximum_length desc

쿼리 

 CREATE TABLE dbo.[테이블명] (
        seq int IDENTITY (1, 1) NOT NULL,
       id varchar(20) NOT NULL
 )
 
insert into dbo.[테이블명] (seq,id) values(1,'abc')

IDENTITY_INSERT가 OFF로 설정되면 테이블 '테이블명'의 ID 열에 명시적 값을 삽입할 수 없습니다.

IDENTITY 가 있는 데이터 테이블 생성 후 해당 컬럼 입력 시도 할 경우 에러 발생

아래와 같이 IDENTITY_INSERT ON 처리 후 실행 시 가능해짐

반드시 INSERT 후에 OFF 처리 해야 이후 문제가 안생김

SET IDENTITY_INSERT dbo.[테이블명] ON

insert into dbo.[테이블명] (seq,id) values(1,'abc')

SET IDENTITY_INSERT dbo.[테이블명] OFF

년,월,일,시,분 단위로 그룹핑하여 보기 각종 통계시 활용

select DATEPART(YEAR, adddt) '년'
,DATEPART(MONTH, adddt) '월'
,DATEPART(DAY, adddt) '일'
,DATEPART(HOUR, adddt) '시'
-- ,(DATEPART(MINUTE, adddt) / 10)
,count(*) FRom 테이블명 with(nolock)
 GROUP BY
DATEPART(YEAR, adddt)
,DATEPART(MONTH, adddt) 
,DATEPART(DAY, adddt)
,DATEPART(HOUR, adddt)
--,(DATEPART(MINUTE, adddt) / 10)
order by 1,2,3,4


select TIMEFROMPARTS(
         DATEPART(HOUR, adddt),
         DATEPART(MINUTE, adddt) / 5 * 5, 0,
         0,
         0)
,count(*) FRom 테이블명 with(nolock) 
group by TIMEFROMPARTS(
         DATEPART(HOUR, adddt),
         DATEPART(MINUTE, adddt) / 5 * 5, 0,
         0,
         0)
order by 1

create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

참고 URL : https://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server

로그 서버에 DB 파일 사이즈가 900기가에 육박하여 불필요한 로그를 삭제하고 

DBCC SHRINKFILE(DB명, TRUNCATEONLY)

위와 같이 축소를 했다.

하지만 크게 줄어들지 않은 mdf 파일 사이즈

왜 그렇지 하고 찾아보니 SHRINKFILE 옵션을 찾아보니 

(MS docs 링크)

NOTRUNCATE
target_percent의 지정 여부와 관계없이 데이터 파일의 끝에서 할당된 페이지를 파일 앞의 할당되지 않은 페이지로 이동합니다. 파일 끝의 사용 가능한 공간이 운영 체제에 반환되지 않고, 파일의 물리적 크기가 변경되지 않습니다. 그러므로 NOTRUNCATE를 지정하면 파일이 축소되지 않는 것처럼 보입니다. NOTRUNCATE는 데이터 파일에만 적용되며 로그 파일에는 영향을 주지 않습니다. 이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.

TRUNCATEONLY
파일 끝의 모든 사용 가능한 공간을 운영 체제로 해제하지만 파일 내에서 페이지 이동을 수행하지 않습니다. 데이터 파일은 마지막으로 할당된 익스텐트까지만 축소됩니다. TRUNCATEONLY로 지정되면 target_size가 무시됩니다.
TRUNCATEONLY 옵션은 로그에 있는 정보를 이동시키지 않습니다. 하지만 로그 파일의 끝에서 비활성 상태의 VLF를 제거합니다. 이 옵션은 FILESTREAM 파일 그룹 컨테이너에서 지원되지 않습니다.

TRUNCATEONLY 옵션으로는 중간중간에 비어있는 공간에 대해서 해제를 하지 못한다는 것.

NOTRUNCATE 옵션으로 중간중간 비어있는 공간들을 당겨주고

TRUNCATEONLY 옵션으로 해제해주어야 원하는 결과를 얻을 수 있었다.

DBCC SHRINKFILE(DB명,NOTRUNCATE) 
-- DB 빈 공간 정리 마치 조각 모음과 같다. ( 빈 공간은 그대로 남아있다 )

DBCC SHRINKFILE(DB명, TRUNCATEONLY) 
-- 빈 여유 공간 제거 ( 사이즈 축소 )

SQL Server 2000 이상

SELECT * FROM dbo.sysdatabases

SQL Server 2005 이상

SELECT * FROM sys.databases

DB 이름만

EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'

DB 이름 , 소유자 , 상태 , 크기  등과 함께

EXEC sp_helpdb

DB 이름 , 크기

EXEC sp_databases

ALTER DATABASE 디비명 SET OFFLINE WITH ROLLBACK IMMEDIATE



불완전한 트랜잭션이 롤백되고 있습니다. 예상 롤백 완료율: 0%.
불완전한 트랜잭션이 롤백되고 있습니다. 예상 롤백 완료율: 100%.

오프라인 오래 걸려서 구글링 하니 나옴..

ALTER DATABASE 는 트랜잭션이 Commit , Rollback 되어야 실행되기 때문에 처리가 지연된다.

https://ribin.tistory.com/286

단순 DB status 를 확인하기 위해 찾았으나

collation , status , user Access 등.. 활용도 있는 값들이 있음.

state_desc : ONLINE / OFFLINE

state : 0 = ONLINE 
         1 = RESTORING 
         2 = 복구 | SQL Server 2008 를 통해 SQL Server 2017
         3 = RECOVERY_PENDING | SQL Server 2008 를 통해 SQL Server 2017
         4 = SUSPECT 
         5 EMERGENCY = | SQL Server 2008 를 통해 SQL Server 2017
         6 = 오프 라인 | SQL Server 2008 를 통해 SQL Server 2017
         7 = 복사| Azure SQL 데이터베이스 활성 지리적 복제 
         10 = OFFLINE_SECONDARY | Azure SQL 데이터베이스 활성 지리적 복제 

SELECT name,state_desc,state,* FROM sys.databases 
WHERE state_desc = 'ONLINE'

자세한 추가 정보는 :

https://docs.microsoft.com/ko-kr/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017