MSSQL 쿼리 모음

[원쿼리맨]

MSSQL 쿼리 모음




-- 버전확인
select @@VERSION

-- top N 쿼리
select top 10 * from sys.columns
order by name desc

-- select * from tab
select * from INFORMATION_SCHEMA.TABLES
order by 3

-- 테이블, 컬럼 정보
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG = :TABLE_CATALOG
   and TABLE_SCHEMA  = :TABLE_SCHEMA
   and TABLE_NAME    = :TABLE_NAME
order by ORDINAL_POSITION

-- 테이블, 컬럼 정보2
select COLUMN_NAME
     , ORDINAL_POSITION as position
, DATA_TYPE
     , case data_type when 'nvarchar' then cast(CHARACTER_MAXIMUM_LENGTH as varchar)
                      when 'numeric'  then cast(NUMERIC_PRECISION as varchar) + ', '
                     + cast(NUMERIC_SCALE as varchar)
      when 'datetime' then null
       end as length
     , IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG = :TABLE_CATALOG
   and TABLE_SCHEMA  = :TABLE_SCHEMA
   and TABLE_NAME    = :TABLE_NAME
order by ORDINAL_POSITION

-- 테이블, 컬럼 정보3
select a.COLUMN_NAME
     , c.value as comment
     , a.ORDINAL_POSITION as position
, a.DATA_TYPE
     , case a.data_type when 'varchar'  then cast(a.CHARACTER_MAXIMUM_LENGTH as varchar)
                    when 'nvarchar' then cast(a.CHARACTER_MAXIMUM_LENGTH as varchar)
                        when 'numeric'  then cast(a.NUMERIC_PRECISION as varchar) + ', '
                       + cast(a.NUMERIC_SCALE as varchar)
                        when 'text'     then 'text'
        when 'datetime' then 'datetime'
       end as length
     , a.IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS a
left join sys.columns b
   on b.object_id = OBJECT_ID(a.table_name)
  and a.ORDINAL_POSITION = b.column_id
left join sys.extended_properties c
   on b.object_id = c.major_id
  and b.column_id = c.minor_id
where a.TABLE_CATALOG = :TABLE_CATALOG
   and a.TABLE_SCHEMA  = :TABLE_SCHEMA
   and a.TABLE_NAME    = :TABLE_NAME
order by a.ORDINAL_POSITION

-- 테이블, 컬럼 정보4
sp_help :table_name

-- 테이블, 컬럼 정보5(마지막)
sp_columns @table_name = :table_name

-- with 문장
with t as (
select 0 a
union all
select 1 a)
select * from t

-- sysdate
select GETDATE()

-- 현재 시간을 yyyymmdd 포맷으로
select format(GETDATE(), 'yyyyMMdd')

-- 현재 시간을 yyyy.mm.dd 포맷으로
select format(GETDATE(), 'yyyy.MM.dd')

-- 현재 시간을 yyyy-mm-dd 포맷으로
select format(GETDATE(), 'yyyy-MM-dd')

-- random number generator(not truly)
select top 10 rand(checksum(newid())) from sys.columns

-- rownum 구현
select top 10 row_number() over (order by name) as rnum from sys.columns

-- 달력
with t(lev, ym) as (
select 1 lev, convert(date, '201210'+'01', 112) ym
union all
select 1+lev, convert(date, '201210'+'01', 112) from t where DATEADD(day, lev, ym)<=EOMONTH(ym)
)
select MIN(IIF (DATEPART (weekday, DATEADD(day, lev-1, ym)) = 1, lev, null)) SUN
     , MIN(IIF (DATEPART (weekday, DATEADD(day, lev-1, ym)) = 2, lev, null)) MON
     , MIN(IIF (DATEPART (weekday, DATEADD(day, lev-1, ym)) = 3, lev, null)) TUE
     , MIN(IIF (DATEPART (weekday, DATEADD(day, lev-1, ym)) = 4, lev, null)) WED
     , MIN(IIF (DATEPART (weekday, DATEADD(day, lev-1, ym)) = 5, lev, null)) THU
     , MIN(IIF (DATEPART (weekday, DATEADD(day, lev-1, ym)) = 6, lev, null)) FRI
     , MIN(IIF (DATEPART (weekday, DATEADD(day, lev-1, ym)) = 7, lev, null)) SAT
  from t
group by datepart(week, DATEADD(day, lev-1, ym))
order by 1

-- 테이블, 컬럼 코멘트 추가
EXEC sp_addextendedproperty
@name = 'Caption', @value = '테스트',
@level0Type = 'Schema', @Level0Name = dbo,
@level1Type = 'Table', @Level1Name = 'test',
@level2Type = 'Column', @Level2Name = 'b'

-- 테이블, 컬럼 코멘트 확인
select * from sys.extended_properties
where major_id = OBJECT_ID('test')

-- 변수 생성
select char(9) + 'private '
   + case when data_type like '%char'
            or data_type in ('text', 'datetime') then 'String'
          when data_type in ('numeric', 'money') then 'double'
  when DATA_TYPE = 'int'                 then 'int'
     end + ' '
   + lower(COLUMN_NAME)
   + ';' as vo
  from INFORMATION_SCHEMA.COLUMNS
where table_name=:table_name
order by ORDINAL_POSITION

-- XML property 생성
select char(9) + char(9) + '' as prop
  from INFORMATION_SCHEMA.COLUMNS
where table_name=:table_name
order by ORDINAL_POSITION