뚱2's Story

링크 : http://msdn.microsoft.com/ko-kr/library/ms189783.aspx

링크 : http://jajking-textcube.blogspot.kr/2009/08/mssql-%EC%95%8C%EB%A9%B4-%EC%9C%A0%EC%9A%A9%ED%95%9C-%EC%8B%9C%EC%8A%A4%ED%85%9C-%ED%85%8C%EC%9D%B4%EB%B8%94%EC%A0%95%EB%B3%B4.html 

링크 (sys.objects) : http://pointnet.tistory.com/206 

-- yyyymmddhhmiss 형식

SELECT REPLACE(

REPLACE(

REPLACE(

CONVERT(VARCHAR, GETDATE(), 120), ' ' , ''), '-', ''), ':', '')

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022 
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

OpenSqlServerPort.bat

-- 2008 이전

DECLARE @test INT

SET @test = 1

-- 2008 이후

DECLARE @test INT = 1

--파일이 축소되지 않음

--축소 작업이 오류 없이 실행되지만 파일 크기가 변경되지 않은 것처럼 보이면 다음 작업 중 하나를 수행하여 파일에 제거할 여유 공간이 있는지 확인합니다.

--다음 쿼리를 실행합니다.

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sys.database_files;

go

--2. 로그 파일을 지정한 대상 크기로 축소

 

--다음 예에서는 AdventureWorks2008R2 데이터베이스에 있는 로그 파일을 1MB로 축소합니다. DBCC SHRINKFILE 명령이 파일을 축소할 수 있도록 먼저 데이터베이스 복구 모델을 SIMPLE로 설정하여 파일을 자릅니다.

go

USE [SFC_HRPY];

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE [SFC_HRPY]

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE ([SFC01_log], 1);

GO

-- Reset the database recovery model.

ALTER DATABASE [SFC_HRPY]

SET RECOVERY FULL;

GO

링크 : http://technet.microsoft.com/ko-kr/library/ms175043(v=sql.105).aspx 

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [SBO-COMMON]
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ([SBO-COMMON_log], 1);
GO
-- Reset the database recovery model.
ALTER DATABASE [SBO-COMMON]
SET RECOVERY FULL;
GO

테이블 스키마 정보를 읽어서 VO 객체를 자동으로 만들려고 하는데 MSSQL 테이블 스키마 정보를 읽어오는 쿼리가 필요했다.

회사 과장님이 만들어 주신걸 잘 활용 감사합니다. ㅎㅎ

 SELECT C.TABLE_NAME AS [Table]                                                                               
      , '' Entity                                                                                             
      , C.COLUMN_NAME AS [Column]                                                                             
      , DATA_TYPE AS [Datatype]                                                                               
      , '' Attribute                                                                                          
      , ISNULL (K.PK, '') AS PK                                                                               
      , ISNULL (X.[Identity], '') AS [Identity]                                                               
      , C.IS_NULLABLE AS NULLABLE                                                                             
      , ISNULL (K.FK, '') AS FK                                                                               
      , ORDINAL_POSITION AS COLIDX                                                                            
   FROM       (SELECT TABLE_NAME                                                                              
                    , COLUMN_NAME                                                                             
                    , CASE WHEN IS_NULLABLE = 'YES' THEN '' ELSE 'NOT' END AS IS_NULLABLE                     
                    , DATA_TYPE                                                                               
                    , CASE                                                                                    
                         WHEN DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'char', 'nchar')                           
                         THEN                                                                                 
                              DATA_TYPE                                                                       
                            + '('                                                                             
                            + CONVERT (VARCHAR (10), ISNULL (CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION))    
                            + ')'                                                                             
                         WHEN DATA_TYPE IN ('DECIMAL')                                                        
                         THEN                                                                                 
                              DATA_TYPE                                                                       
                            + '('                                                                             
                            + CONVERT (VARCHAR (10), NUMERIC_PRECISION)                                       
                            + ', '                                                                            
                            + CONVERT (VARCHAR (10), NUMERIC_SCALE)                                           
                            + ')'                                                                             
                         ELSE                                                                                 
                            ''                                                                                
                      END                                                                                     
                         AS DATA_SIZE                                                                         
                    , ORDINAL_POSITION                                                                        
                    , COLUMN_DEFAULT                                                                          
                 FROM INFORMATION_SCHEMA.COLUMNS) AS C                                                        
           LEFT JOIN                                                                                          
              (SELECT COLUMN_NAME                                                                             
                    , MAX (PK) AS PK                                                                          
                    , MAX (FK) AS FK                                                                          
                    , MAX (INX) AS INX                                                                        
                    , TABLE_NAME                                                                              
                 FROM (SELECT C.COLUMN_NAME                                                                   
                            , K.TABLE_NAME                                                                    
                            , CASE WHEN K.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'Yes' END AS PK                
                            , CASE WHEN K.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'Yes' END AS FK                
                            , CASE WHEN K.CONSTRAINT_TYPE = 'INDEX' THEN 'Yes' END AS INX                     
                         FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS K                                    
                              INNER JOIN                                                                      
                                 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS C                              
                              ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME) AS K                                  
               GROUP BY TABLE_NAME                                                                            
                      , COLUMN_NAME) AS K                                                                     
           ON C.COLUMN_NAME = K.COLUMN_NAME AND C.TABLE_NAME = K.TABLE_NAME                                   
        LEFT JOIN                                                                                             
           (SELECT SO.name AS TableName                                                                       
                 , IC.NAME AS ColumnName                                                                      
                 , 'Yes' AS [Identity]                                                                        
              FROM    sys.identity_columns IC                                                                 
                   INNER JOIN                                                                                 
                      sysObjects SO                                                                           
                   ON SO.id = IC.[object_id]) X                                                               
        ON C.TABLE_NAME = X.TableName AND C.COLUMN_NAME = X.ColumnName                                        
 ORDER BY C.TABLE_NAME                                                                                        
        , C.ORDINAL_POSITION     

테이블스키마정보.sql

-- 유니코드로 언어변경
ALTER DATABASE [KISS] COLLATE Korean_Wansung_Unicode_CI_AS