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


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])

테이블 사이즈 얻어오기

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


-- 컬럼명 쿼리 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