作為一個(gè)"浸淫" Oracle 數(shù)據(jù)庫(kù)很久的人來(lái)說(shuō), 突然轉(zhuǎn)入 T-SQL, 也就是 MSSQL , 工作中經(jīng)常用的查詢(xún)和 MSMS 使用備忘如下 :
字符串的處理拼接
- 2017-12-8
-
T-SQL中 引號(hào)中 兩個(gè)引號(hào)代表一個(gè)引號(hào) - 傳入?yún)?shù)需要``區(qū)域表示 , 而內(nèi)容也包括引號(hào), 此時(shí)兩個(gè)單引號(hào)表示一個(gè)引號(hào), 這種表述在存儲(chǔ)過(guò)程字符串拼接中也生效. 例如:
EXEC dbo.sp_OutputData
@tablename = 'W1ArchiveItem' , -- sysname
@Where = 'ArchiveItemID=''9C0D8E88-543B-4E7D-8D89-8B7321469B79''' , -- varchar(4000)
@OrderBy = '' , -- varchar(4000)
@IfNotExistsColumn = '' -- varchar(4000)
set @ExSQL = '
SELECT a.WageTableItemID
,a.[WageTableID]
,a.[ArchiveID]
,b.ArchiveNO '
+ case when len(@AdtnFldList) > 1 then ',' + char(10) + @AdtnFldList else '' end
+ char(10)
+ ' from [W1WageTableItem] a
inner join [W1Archive] b on A.ArchiveID = b.ArchiveID
where a.WageTableID = ''' + @p_WageTableID + '''
and (b.ArchiveNO like ''%' + @p_KeyWords + '%'' or b.EmployeeName like ''%' + @p_KeyWords + '%'')'
- 一篇有價(jià)值的參考網(wǎng)址; http://blog.51cto.com/qianzhang/1204630
其他常用
--1. 切換對(duì)應(yīng)的庫(kù)連接
USE CYBPMCOSTDB1041_Dev
--2. 查看存儲(chǔ)過(guò)程內(nèi)容
SP_HELPTEXT 'V0DataDict'
--3. 查看表列內(nèi)容
sp_columns 'A1Org'
--4. 查看數(shù)據(jù)庫(kù)表描述, 行列轉(zhuǎn)換過(guò), 可以看到包括備注之類(lèi)(自定義視圖, 后面附視圖內(nèi)容)
SELECT *FROM dbo.V0DataDict WHERE name='A1Org'
--5.
--執(zhí)行帶輸出參數(shù)的存儲(chǔ)過(guò)程
DECLARE @p_RecTotal int
EXEC PM1SpecialEqptRegisterPage
@p_OrgID = 'ED6989D2-B7CB-478B-9E37-D0C15D720D6A',
@p_ManageCode = '',
@p_FilterStr='',
@p_PageIndex = 0,
@p_PageSize= 25,
@p_OrderField = 'BizDate',
@p_OrderType = 1,
@p_IsLargeEqpt = -1,
@p_RecTotal = @p_RecTotal OUT
SELECT @p_RecTotal
- 附上對(duì)應(yīng)視圖的內(nèi)容
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
CREATE VIEW [V0DataDict]
AS
SELECT TOP 100 PERCENT
*
FROM ( SELECT ISNULL(CAST(UserTable.value AS VARCHAR(256)), '') AS 表名 ,
REPLACE(REPLACE(REPLACE(CAST(UserTableColumn.value AS VARCHAR(256)),
' ', ''), CHAR(13), ''),
CHAR(10), '') AS 列名 ,
UserTableColumn.colname AS 列編碼 ,
CAST(UserTableColumn.colid AS VARCHAR(10)) AS 列序 ,
UserTableColumn.IsPKey AS 主鍵 ,
UserTableColumn.typename AS 數(shù)據(jù)類(lèi)型 ,
CAST(UserTableColumn.length AS VARCHAR(10)) AS 寬度 ,
CASE WHEN UserTableColumn.typename NOT IN ( 'decimal',
'numeric',
'money',
'smallmoney',
'float', 'real' )
THEN ''
ELSE CAST(ISNULL(UserTableColumn.scale, '') AS VARCHAR(10))
END AS 小數(shù)位 ,
CASE WHEN UserTableColumn.typename NOT IN ( 'decimal',
'numeric',
'money',
'smallmoney',
'float', 'real' )
THEN ''
ELSE CAST(ISNULL(UserTableColumn.prec, '') AS VARCHAR(10))
END AS 精度 ,
ISNULL(CONVERT(VARCHAR(256), UserTableColumn.text), '') AS 默認(rèn)值 ,
ISNULL(CASE CONVERT(BIT, ( UserTableColumn.status & 8 ))
WHEN 1 THEN '是'
ELSE ''
END, '') AS 可空 ,
ISNULL(CASE CONVERT(BIT, ( UserTableColumn.status
& 0x80 ))
WHEN 1 THEN '是'
ELSE ''
END, '') AS 自增長(zhǎng) ,
UserTable.name ,
3 AS ListOrder ,
UserTableColumn.colid
FROM ( SELECT sys.sysobjects.id ,
sys.sysobjects.name ,
sys.extended_properties.value
FROM sys.sysobjects
LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.major_id = sys.sysobjects.id
AND sys.extended_properties.minor_id = 0
WHERE sys.sysobjects.type = 'U' /*and
convert( varchar(10), sys.sysobjects.crdate, 121 ) = convert( varchar(10), getdate(), 121 )*/
) UserTable
INNER JOIN ( SELECT sys.syscolumns.id ,
sys.syscolumns.colid ,
sys.syscolumns.name AS colname ,
sys.extended_properties.value ,
sys.systypes.name AS typename ,
sys.syscolumns.length ,
sys.syscolumns.scale ,
sys.syscolumns.prec ,
sys.syscomments.text ,
sys.syscolumns.status ,
PKeyColumns.object_id ,
PKeyColumns.column_id ,
CASE WHEN PKeyColumns.column_id IS NULL
THEN ''
ELSE '是'
END AS IsPKey
FROM sys.syscolumns
LEFT OUTER JOIN sys.syscomments ON sys.syscolumns.cdefault = sys.syscomments.id
LEFT OUTER JOIN sys.systypes ON ( sys.syscolumns.usertype = sys.systypes.usertype )
AND ( sys.syscolumns.xusertype = sys.systypes.xusertype )
LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.major_id = sys.syscolumns.id
AND sys.extended_properties.minor_id = sys.syscolumns.colid
LEFT OUTER JOIN ( SELECT
sys.index_columns.object_id ,
sys.index_columns.column_id
FROM
sys.index_columns
INNER JOIN sys.indexes ON sys.indexes.index_id = sys.index_columns.index_id
AND sys.indexes.object_id = sys.index_columns.object_id
WHERE
sys.indexes.is_primary_key = 1
) PKeyColumns ON PKeyColumns.object_id = sys.syscolumns.id
AND PKeyColumns.column_id = sys.syscolumns.colid
) UserTableColumn ON UserTableColumn.id = UserTable.id
UNION ALL
SELECT TOP 100 PERCENT
'' AS 表名 ,
ISNULL(CAST(sys.extended_properties.value AS VARCHAR(256)),
'') AS 列名 ,
sys.sysobjects.name AS 列編碼 ,
'' AS 列序 ,
'' AS 主鍵 ,
'' AS 數(shù)據(jù)類(lèi)型 ,
'' AS 寬度 ,
'' AS 小數(shù)位 ,
'' AS 精度 ,
'' AS 默認(rèn)值 ,
'' AS 可空 ,
'' AS 自增長(zhǎng) ,
sys.sysobjects.name ,
1 AS ListOrder ,
0 AS colid
FROM sys.sysobjects
LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.major_id = sys.sysobjects.id
AND sys.extended_properties.minor_id = 0
WHERE sys.sysobjects.type = 'U' --and
--convert( varchar(10), sys.sysobjects.crdate, 121 ) = convert( varchar(10), getdate(), 121 )
UNION ALL
SELECT TOP 100 PERCENT
'表名' AS 表名 ,
'列名' AS 列名 ,
'列編碼' AS 列編碼 ,
'列序' AS 列序 ,
'主鍵' AS 主鍵 ,
'數(shù)據(jù)類(lèi)型' AS 數(shù)據(jù)類(lèi)型 ,
'寬度' AS 寬度 ,
'小數(shù)位' AS 小數(shù)位 ,
'精度' AS 精度 ,
'默認(rèn)值' AS 默認(rèn)值 ,
'可空' AS 可空 ,
'自增長(zhǎng)' AS 自增長(zhǎng) ,
sys.sysobjects.name ,
2 AS ListOrder ,
0 AS colid
FROM sys.sysobjects
LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.major_id = sys.sysobjects.id
AND sys.extended_properties.minor_id = 0
WHERE sys.sysobjects.type = 'U' --and
--convert( varchar(10), sys.sysobjects.crdate, 121 ) = convert( varchar(10), getdate(), 121 )
UNION ALL
SELECT TOP 100 PERCENT
'' AS 表名 ,
'' AS 列名 ,
'' AS 列編碼 ,
'' AS 列序 ,
'' AS 主鍵 ,
'' AS 數(shù)據(jù)類(lèi)型 ,
'' AS 寬度 ,
'' AS 小數(shù)位 ,
'' AS 精度 ,
'' AS 默認(rèn)值 ,
'' AS 可空 ,
'' AS 自增長(zhǎng) ,
sys.sysobjects.name ,
4 AS ListOrder ,
0 AS colid
FROM sys.sysobjects
LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.major_id = sys.sysobjects.id
AND sys.extended_properties.minor_id = 0
WHERE sys.sysobjects.type = 'U' /*and
convert( varchar(10), sys.sysobjects.crdate, 121 ) = convert( varchar(10), getdate(), 121 ) */
) A
WHERE A.name LIKE '[A-Z][0-9]%'
ORDER BY A.name ASC ,
A.ListOrder ASC ,
A.colid ASC;
GO
- 另外發(fā)現(xiàn)很多人或者我這種半路開(kāi)始使用MSSQL的經(jīng)常誤區(qū). 如圖所示:
- 一般來(lái)說(shuō)剛打開(kāi)自動(dòng)登陸的就是對(duì)象瀏覽器. 至于當(dāng)前界面查詢(xún)使用哪個(gè)鏈接 是分開(kāi)的. 不然一直在對(duì)象瀏覽器里面切來(lái)切去, 是跟當(dāng)前查詢(xún)沒(méi)有任何關(guān)系的.
Paste_Image.png
