〔SQL〕一次重新設定流水序號 / UPDATE ROW_NUMBER()

範例：執行將依據指定排序條件，將特定欄位重設流水序號

WITH UT as (SELECT SCode, L, ROW_NUMBER() OVER (ORDER BY SCODE) as RN FROM SP03 WHERE sp02_scode=100) UPDATE UT SET L=RN

Read More

〔SQL〕一次取得所有資料表內含資料筆數 / Select Table Rows

範例：執行將顯示資料庫內所有資料表名及內含資料筆數

SELECT T .name AS [資料表名] ,

       I.rows AS [筆數]

FROM   sys .tables AS T

       INNER JOIN sys. sysindexes AS I

       ON T .object_id = I . id  AND I .indid < 2

ORDER  BY T. name

Read More

〔SQL〕UPDATE SELECT 利用SELECT做大量更新

範例：將訂單表身(OD00D1)出貨日，批次更新為訂單表頭(OD00H)出貨日

UPDATE

    OD00D1

SET

    OD00D1. DeliveryDate = OD00H. DeliveryDate,

    OD00D1. ProductDate = DATEADD( Day,-7 ,OD00H. DeliveryDate)

FROM

    OD00D1

    INNER JOIN OD00H

        ON OD00D1 .ODNo = OD00H .ODNo

WHERE

    OD00D1. ODNo = 'OD1606-00002'

Read More

〔SQL〕一次取得資料庫欄位名稱、型態等設定 / Select Table Column Type

範例：列出資料庫內所有資料表及其欄位名稱等相關設定

SELECT 

    a.TABLE_NAME                 as 表格名稱 , 

    b.COLUMN_NAME                as 欄位名稱 , 

    b.DATA_TYPE                  as 資料型別 , 

    b.CHARACTER_MAXIMUM_LENGTH   as 最大長度, 

    b.COLUMN_DEFAULT             as 預設值 , 

    b.IS_NULLABLE                as 允許空值 , 

    ( 

        SELECT 

            value 

        FROM 

            fn_listextendedproperty (NULL, 'schema', 'dbo', '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 , ordinal_position   

Read More