퇴근5분전


 
DECLARE @TABLE_NAME NVARCHAR(776)
SET @TABLE_NAME = PARSENAME( '테이블 명' , 1 )

select COUNT( column_id ) from sys.columns
where OBJECT_ID = OBJECT_ID(@TABLE_NAME)

SELECT
a.name, b.value,
case when b.name is null then '' else
'EXEC sys.sp_dropextendedproperty '+
'@name=N''MS_Description'', '+
'@level0type=N''SCHEMA'', @level0name=N''dbo'', '+
'@level1type=N''TABLE'', @level1name=N'''+OBJECT_NAME(a.OBJECT_ID)+''', '+
'@level2type=N''COLUMN'', @level2name=N'''+a.NAME+'''' end as DropEx,

'EXEC sys.sp_addextendedproperty '+
'@name=N''MS_Description'', @value=N'''+'{0}'+''', '+
'@level0type=N''SCHEMA'', @level0name=N''dbo'', '+
'@level1type=N''TABLE'', @level1name=N'''+OBJECT_NAME(a.OBJECT_ID)+''', '+
'@level2type=N''COLUMN'',@level2name=N'''+a.NAME+'''' as AddEx
FROM sys.columns a
left outer join
(
 SELECT OBJECT_ID( @TABLE_NAME ) object_id , objtype, objname , name, value
 FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TABLE_NAME, 'column', default)
-- order by object_id, objname COLLATE  Korean_Wansung_CI_AS asc
) b
on a.object_id = b.object_id and a.name COLLATE  Korean_Wansung_CI_AS = b.objname COLLATE  Korean_Wansung_CI_AS
WHERE a.OBJECT_ID = OBJECT_ID(@TABLE_NAME)


-- 테이블 설명 추가...

EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'테이블 추가 설명 ex',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TABLE',  @level1name =  '테이블 명' ;

SELECT OBJECT_ID(  '테이블 명' ) object_id , objtype, objname , name, value
FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table',  '테이블 명', default, default)
where name = 'MS_Description'

 


전에 만들어둔게 있었는데....

문제가 조금 있으니 쿼리를 수정함...

이 프로시져는 등록한 데이타 베이스에서만 사용이 가능하다.

전체 DB에서 사용할 수 있는 방법을 찾아봐야겠다.

-- TAG 검색 되게 PRMS, prms

수정 PK , FK ... 정보 출력.

/*   
작성자 : 윤지송    
   
테이블 컬럼의 속성 보는 쿼리!!   
   
   
SqlManagement Studio에 도구 -> 옵션 -> 일반탭 : 키보드    
   
매크로 지정하는 화면에서    
   
키에 exec SP_TABLE_COLUMN_INFO 를 입력해두고...   
   
쿼리실행창에서 보고싶은 테이블이름 셀렉팅 하고 매크로 키를 누르면 프로시져가 실행되어진다.    
   
단, 키등록후 매니져를 껐다 켜야 되드랑!!    
   
*/   
ALTER PROC DBO.SP_TABLE_COLUMN_INFO   
(   
 @TABLENAME nvarchar(776) = NULL   
)   
AS
 
 
 select distinct
  c.object_id , c.name,
   ltrim( rtrim( case when isnull( index_col( @TABLENAME , i.index_id, C.column_id ),'') <> '' then 'PK' else '' end + ' '+
  case when isnull( f.constraint_column_id , '' ) <> '' then 'FK' else '' end )) as [KEY] ,
  isnull(e.value,'') ExName , isnull( t.name ,'') TypeName,   
  c.COLUMN_ID,                
  CONVERT( VARCHAR,  c.NAME ) AS[NAME],               
  UPPER(t.NAME) AS [DTYPE] ,               
  CASE WHEN UPPER(t.NAME) IN ('CHAR','VARCHAR','NVARCHAR','NCHAR','TEXT' )  THEN CASE c.MAX_LENGTH WHEN -1 THEN '(MAX)'    
          ELSE '('+ CONVERT( VARCHAR, c.MAX_LENGTH / ( CASE WHEN LEFT(t.NAME,1) = 'N' THEN 2 ELSE 1 END ) ) +')'     
        END    
   WHEN UPPER(t.NAME) IN ('NUMERIC','DECIMAL' ) THEN '('+ CONVERT(VARCHAR, c.PRECISION) + ',' + CONVERT(VARCHAR, c.SCALE)+')'   
   ELSE ''    
  END AS [LEN],               
  '-- ' + isnull( CONVERT( VARCHAR,  e.VALUE  ) , c.Name ) AS [DESC],               
                
  '@'+CONVERT( VARCHAR,  c.NAME )+' ' + UPPER(t.NAME) +  CASE WHEN UPPER(t.NAME) IN ('CHAR','VARCHAR','NVARCHAR','NCHAR') THEN CASE c.MAX_LENGTH WHEN -1 THEN '(MAX)'    
            ELSE '('+ CONVERT( VARCHAR, c.MAX_LENGTH / ( CASE WHEN LEFT(t.NAME,1) = 'N' THEN 2 ELSE 1 END ) ) +')'    
          END    
           WHEN UPPER(t.NAME) IN ('NUMERIC','DECIMAL' ) THEN '('+ CONVERT(VARCHAR, c.PRECISION) + ',' + CONVERT(VARCHAR, c.SCALE)+')'   
           ELSE ''    
      END +    
   ', -- ' + isnull( CONVERT( VARCHAR,  e.VALUE  ) , '') AS  PROC_PRMS,   
             
  '@'+CONVERT( VARCHAR,  c.NAME + ',' ) AS [PRMS] ,          
          
  'AND'  AS [AND],         
  CONVERT( VARCHAR,  c.NAME + ' = ' ) + '@'+CONVERT( VARCHAR,  c.NAME  ) AS [WPRMS]       
  ,   
  '' as ['        공백         ']   
  ,c.*       
 from sys.columns c left outer join sys.extended_properties e   
 on c.Object_id = e.MAJOR_ID and c.Column_Id = e.Minor_id and e.CLASS = 1   
 left outer join SYS.TYPES t     
 on c.SYSTEM_TYPE_ID = t.SYSTEM_TYPE_ID AND t.NAME <> 'SYSNAME'   
 left outer join sys.index_columns i
 on c.object_id = i.object_id and c.column_id = i.column_id
 left outer join  sys.foreign_key_columns f
 on c.object_id = f.parent_object_id and c.column_id = f.parent_column_id
 where c.Object_id = OBJECT_ID( @TABLENAME )   
 ORDER BY C.column_id
   
SELECT OBJECT_ID(OBJNAME) OBJECT_ID , objtype, objname , name, value 
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TABLENAME, default, default)

 



  내부 컨텐츠를 변경하는 방법을 구현한 것임.

매번 쓸때마다 구현하기도 구찮긴 한뎅... 이제 플그램 많이 안할것 같으니 적어만 둔다.


public partial class Form1 : Form
    {
        List<Control> Contents = new List<Control>();
      int currentControlIndex = 0;

        public Form1()
        {
            InitializeComponent();
            Contents.Add(new ContenControl() { Text = "TXT01" });
            Contents.Add(new ContenControl() { Text = "TXT02" });
            Contents.Add(new ContenControl() { Text = "TXT03" });
            Contents.Add(new ContenControl() { Text = "TXT04" });
            Contents.Add(new ContenControl() { Text = "TXT05" });
            Contents.Add(new ContenControl() { Text = "TXT06" });
            Contents.Add(new ContenControl() { Text = "TXT07" });
            Contents.Add(new ContenControl() { Text = "TXT08" });

            foreach (Control ctrl in Contents)
            {
                this.panel1.Controls.Add(ctrl);
            }
            UpdateButtonStates();
        }

        private void NextBtn_Click(object sender, EventArgs e)
        {
            // Next
            Control Current = GetContentControl(+1);
            Current.BringToFront();
            UpdateButtonStates();
        }

        private void UpdateButtonStates()
        {
            NextBtn.Enabled = currentControlIndex < Contents.Count - 1;
            PrevBtn.Enabled = 0 < currentControlIndex;
        }

        private Control GetContentControl(int idx)
        {
            Control ctrl = Contents[currentControlIndex];
            if (idx < 0)
            {
                if (currentControlIndex + idx < 0)
                {
                    idx = 0;
                }
            }
            else if (0 < idx)
            {
                if (Contents.Count - 1 < currentControlIndex + idx)
                {
                    idx = Contents.Count - 1;
                }
            }
            currentControlIndex = currentControlIndex + idx;
            ctrl = Contents[ currentControlIndex ];
            return ctrl;
        }

        private void PrevBtn_Click(object sender, EventArgs e)
        {
            // Prev
            Control Current = GetContentControl(-1);
            Current.BringToFront();
            UpdateButtonStates();
        }
    }


    public class ContenControl : UserControl
    {
        Label lb = new Label();
        public ContenControl()
        {
            this.lb.BorderStyle = BorderStyle.FixedSingle;
            lb.AutoSize = false;
            lb.TextAlign = ContentAlignment.MiddleCenter;
            lb.Dock = DockStyle.Fill;
            this.Controls.Add(lb);
            this.BorderStyle = BorderStyle.FixedSingle;
            this.Dock = DockStyle.Fill;
        }

        public override string Text
        {
            get
            {
                return lb.Text;
            }
            set
            {
                lb.Text = value;
            }
        }
    }

'# 3) .Net ( Vs 2008 ) > C#' 카테고리의 다른 글

ClickOnce UpdateAsync ~!!  (0) 2011.10.31
LINQ] GroupBy  (0) 2011.10.12
Vs2008] 성능 카운터  (0) 2011.08.16
vs2008] WMI 관련..  (0) 2011.08.16
노트북 셋팅..  (0) 2011.08.02