뚱2's Story

테이블 스키마 정보를 읽어서 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