查詢SQL 的DB schema

#找到更好用的語法, 記錄一下.


SELECT tb.TABLE_NAME AS 'TableName' ,CASE WHEN tb.TABLE_TYPE = 'VIEW' THEN (SELECT value FROM sys.fn_listextendedproperty(NULL, 'user', 'dbo', 'view', tb.TABLE_NAME, DEFAULT, DEFAULT) WHERE name = 'MS_Description' AND objtype = 'VIEW') ELSE (SELECT value FROM sys.fn_listextendedproperty(NULL, 'user', 'dbo', 'table', tb.TABLE_NAME, DEFAULT, DEFAULT) WHERE name = 'MS_Description' AND objtype = 'TABLE') END AS 'TableDescription' ,tb.TABLE_TYPE AS 'TableType' ,col.ORDINAL_POSITION AS 'No' ,col.COLUMN_NAME AS 'ColumnName' ,col.DATA_TYPE AS 'DataType' ,CASE WHEN col.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE LTRIM(STR(col.CHARACTER_MAXIMUM_LENGTH,10)) END AS 'Length' ,col.NUMERIC_SCALE AS 'NumericScale' ,col.NUMERIC_PRECISION AS 'NumericPrecision' ,col.COLUMN_DEFAULT AS 'Default' ,col.IS_NULLABLE AS 'Nullable' ,CASE WHEN COLUMNPROPERTY(object_id(tb.TABLE_NAME), col.COLUMN_NAME, 'IsIdentity') = 1 THEN 'YES' ELSE 'NO' END AS 'Identity' ,(SELECT value FROM sys.fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', tb.TABLE_NAME, 'column', DEFAULT) WHERE name = 'MS_Description' AND objtype = 'COLUMN' AND objname COLLATE Chinese_Taiwan_Stroke_CI_AS = col.COLUMN_NAME) AS 'Description' ,CASE WHEN tbc.CONSTRAINT_NAME is not null THEN 'YES' ELSE 'NO' END AS 'PK' ,tbc.CONSTRAINT_NAME AS ' PkConstraint' ,CASE WHEN kcu1.CONSTRAINT_NAME is not null THEN 'YES' ELSE 'NO' END AS 'FK' ,kcu1.CONSTRAINT_NAME AS ' FkConstraint' ,kcu2.TABLE_NAME AS 'FkReferencedTable' ,kcu2.COLUMN_NAME AS 'FkReferencedColumn' FROM INFORMATION_SCHEMA.TABLES tb LEFT JOIN INFORMATION_SCHEMA.COLUMNS col ON (tb.TABLE_NAME = col.TABLE_NAME) LEFT JOIN ( INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu1 ON kcu1.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG AND kcu1.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND kcu1.CONSTRAINT_NAME = rc.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu2 ON kcu2.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG AND kcu2.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA AND kcu2.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME AND kcu2.ORDINAL_POSITION = kcu1.ORDINAL_POSITION ) ON (tb.TABLE_NAME = kcu1.TABLE_NAME AND col.COLUMN_NAME = kcu1.COLUMN_NAME) LEFT JOIN ( INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tbc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS colc ON colc.CONSTRAINT_NAME = tbc.CONSTRAINT_NAME AND colc.TABLE_NAME = tbc.TABLE_NAME AND tbc.CONSTRAINT_TYPE = 'PRIMARY KEY' ) ON (tb.TABLE_NAME = tbc.TABLE_NAME AND col.COLUMN_NAME = colc.COLUMN_NAME) ORDER BY tb.TABLE_NAME, col.ORDINAL_POSITION



參考資料
https://dotblogs.com.tw/wasichris/2016/07/07/004356

gride view 匯出excel

excel 匯出

1.using System.Web.UI;
2.
    public override void VerifyRenderingInServerForm(Control control)
    {
        //'XX'型別 必須置於有 runat=server 的表單標記之中
    }

3.
匯出一 :grid view

 protected void btnExport_Click(object sender, EventArgs e)
    {
        string filename = "satisfyQry_" + DateTime.Now.ToString("yyyyMMdd-HHmm") + ".xls";

        Response.Clear();
        Response.Charset = "utf-8";
        Response.ContentType = "application/vnd.ms-excel";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        Response.Write("");
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
        gvList.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

4.匯出二


      protected void btnExport_Click(object sender, EventArgs e)
    {
        string filename = "satisfyQry_" + DateTime.Now.ToString("yyyyMMdd-HHmm") + ".xls";

        Response.Clear();
        Response.Charset = "utf-8";
        Response.AddHeader(
            "Content-Disposition",
            "attachment;filename=" + Server.UrlEncode(filename)
            );
        Response.ContentType = "application/vnd.ms-excel";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        Response.Write("");
        Response.Write("");
        Response.Write("");

        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter tw = new System.Web.UI.HtmlTextWriter(sw);




                #region header
                sw.WriteLine(@"

非常滿意

        滿意
        普通
        不滿意
        很不滿意
                                     
                                   
                                       
");                #endregion
                #region detail
                for (int i = 0; i < gvList.Rows.Count; i++)
                {
                    sw.WriteLine(" " + gvList.Rows[i].Cells[0].ToString()
                      gvList.Rows[i].Cells.
                        + "
" + gvList.Rows[i]["isi_icusname"].ToString()                        + "
" + gvList.Rows[i]["isi_icuscode"].ToString()                        + "
" + gvListdt.Rows[i]["isi_prerevenue"].ToString()                        + "
" + gvListdt.Rows[i]["isi_equname"].ToString()                        + "
" + gvListdt.Rows[i]["Status"].ToString()                        + "
" + gvListdt.Rows[i]["pos_invno"].ToString()
                        + "
");                }
                #endregion

        sw.WriteLine("
");        Response.Write("

");

          Response.Write(sw.ToString());
            Response.Write("
");        Response.Write("
");

        Response.End();
    }

使用T-SQL 匯出 DB Schema (表格名稱.欄位名稱.資料型別.長度.預設值.是否允許空值.欄位描述)

USE  資料庫名稱;

SELECT a.Table_schema +'.'+a.Table_name   as 表格名稱  
       ,b.COLUMN_NAME                     as 欄位名稱  
       ,b.DATA_TYPE                       as 資料型別  
       ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as 長度  
       ,isnull(b.COLUMN_DEFAULT,'')           as 預設值  
       ,b.IS_NULLABLE                         as 是否允許空值  
       ,( SELECT value  
          FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)  
          WHERE name='MS_Description' and objtype='COLUMN'  
          and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME  
        ) as 欄位描述  
FROM INFORMATION_SCHEMA.TABLES  a  
 LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME  
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION

網路上找到了語法. 

可以產生DD , 但是我沒有習慣在建table 時加上描述, 就少了中文欄位的說明, 

實在是太可惜了, 找時間還是要來把資料表的說明建起來

以後做DD就直接語法匯出整理一下就好了.

資料來源: 

http://alvisprogramming.blogspot.tw/2014/12/t-sql-db-schema.html

抓副檔名

使用者上傳的檔名, 有時會利用系統的編碼重新命名.
此時要抓上傳檔案的副檔名.

但若使用者在檔名若有多加點.來做檔案命名.
就容易抓錯.

在SQL中利用下面的寫法. 可以抓到正確的檔名.

[結果: .doc]
DECLARE @x VARCHAR(32) = 'aaa.bbb.ccc.1.doc'
SELECT Right(@x, CHARINDEX('.', REVERSE(@x)))


[結果: aaa.]
DECLARE @x VARCHAR(32) = 'aaa.bbb.ccc.1.doc'
SELECT left(@x, CHARINDEX('.', REVERSE(@x)))

SQL如何查詢『換行符號』

SQL如何查詢『換行符號』呢？
→ where id like '%'+char(10)+'%'

CHAR可用於將控制字符插入字符串中。下表顯示了一些常用的控制字符。
控制鍵值如下所示
Tab： CHAR(9)
換行： CHAR(10)
ENTER： CHAR(13)

EX:
Select * From Table_Name
Where 1=1 And Column_NAME Like '%'+Cgar(13)+'%'

update base_mapping set basename = replace(basename,Char(10),'')
update base_mapping set basename = replace(basename,Char(13),'')



別人寫的很好. 自己有需要. 
所以備份一下. 
資料來源: http://www.dotblogs.com.tw/dreamweaver0210/archive/2014/05/06/144986.aspx

aspx 抓 Web.config 值

有時網頁要抓取web.config 值.記錄一下方法.


簡單方法一:

1.1 Web.config   appseting 區塊. 寫下.
      

1.2 Aspx 頁面
     意見反應

這樣子就可以了.


後台寫法 方法二:

string engageUrl = System.Web.Configuration.WebConfigurationManager.AppSettings["EngageURL"].ToString();


            string script = string.Format("", engageUrl,  nob_contno);
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "另開視窗", script, false);

SQL: 將分號分隔的客戶編號, 存成table