MSSQL

linux 連 MSSQL

FreeTDS 設定 http://adaam-tw.blogspot.tw/2015/09/freetds-mssql.html

sqlcmd

sqlcmd -U {account} -P {password} -d {database} -i C:\users\xxxx\downloads\xxx.sql -o C:\users\xxxx\downloads\log.txt

取切整數單位日期/時間 DATEADD(HOUR,DATEDIFF(HOUR,0,GETDATE())-1,0) DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())-1,0)

查詢 deadline

SHOW ENGINE INNODB STATUS;

-- 查詢DB所有Table 的相關資訊
SELECT
    a.TABLE_NAME+'.'+b.COLUMN_NAME,
    b.COLUMN_NAME,
    (
        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'
    AND a.TABLE_SCHEMA = 'dbo'
    AND b.TABLE_SCHEMA = 'dbo'
    --AND a.TABLE_NAME = 'customer_prof'
ORDER BY
    a.TABLE_NAME, ordinal_position

--查所有的 SP
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME ASC