MS SQL 자주 사용하는 쿼리 모음

프로그래밍 2018. 4. 12. 15:41


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ALTER TABLE [Board].[InquiryAnswer] ALTER COLUMN [SubCategoryCode] INT NOT NULL
 
ALTER TABLE [Service].[GameItem] ALTER COLUMN [GameItemID]
         DROP NOT FOR REPLICATION;
 
ALTER TABLE [Board].[InquiryAnswer]
   DROP CONSTRAINT [DF__InquiryAn__Creat__6C23FBB3]
 
ALTER TABLE [Board].[InquiryAnswer]
   ADD CONSTRAINT [DF__InquiryAn__Creat__4282C7A2] DEFAULT (getdate()) FOR [CreateDate]
 
CREATE NONCLUSTERED INDEX [IX_NU_MobileApp_ServiceCode]
    ON [Service].[MobileApp]([ServiceCode] ASC);
 
DROP INDEX [IX_NU_MobileApp_ServiceCode]
    ON [Service].[MobileApp]
 
ALTER TABLE [Service].[Service]  
ADD CONSTRAINT [FK_Service_ServiceType] FOREIGN KEY ([ServiceTypeCode])    
    REFERENCES [Service].[ServiceType] ([ServiceTypeCode])

테이블 사이즈 얻어오기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
declare @t table
(
  name nvarchar(50),
  rows varchar(10),
  reserved varchar(10),
  data varchar(10),
  index_size varchar(10),
  unused varchar(10)
)
 
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
 
name                                               rows       reserved   data       index_size unused
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
ServiceConcurrent                                  7565128    334224 KB  333048 KB  1096 KB    80 KB
ErrorLog                                           114503     134032 KB  133544 KB  408 KB     80 KB
Membership                                         469172     132048 KB  131560 KB  392 KB     96 KB


1
2
3
4
5
6
7
8
9
10
11
-- 컬럼명 쿼리
SELECT *
        FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME LIKE '%mail%'
        order by TABLE_NAME
 
-- 프로시저 내용 쿼리
SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%mail%'
        order by ROUTINE_NAME


Posted by 약올랑
openclose

티스토리툴바