VBS/ PowerShell 自动更新SQL SEVER Management Studio Query 模板

最新推荐文章于 2020-11-17 17:29:58 发布

十林

最新推荐文章于 2020-11-17 17:29:58 发布

阅读量869

点赞数 1

本文链接：https://blog.csdn.net/linxtong/article/details/47445313

版权

VBS 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

SQL

3 篇文章 0 订阅

订阅专栏

2019-08-15 更新

添加一个用powershell 实现。更改SQL Server Management Studio 18.0的query 模板

#
# Script1.ps1
# C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SqlWorkbenchProjectItems\Sql
#
$date= "{0:yyyy-MM-dd}" -f (get-date)
$uri = "https://dict.youdao.com/infoline/web?mode=publish&client=web&keyfrom=dict2.index&startDate={0}" -f $date
Write-Host "Call $uri"
$header = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$header.Add("Accept", "application/json")
$response = Invoke-WebRequest -Method Get -Uri $uri -header $header
# Check response status code
#stopScript -statusCode $response.StatusCode -message "Cannot list $resourceType."
#$responseContent = $($response.Content | ConvertFrom-Json).Value
$responseContent = $response.Content | ConvertFrom-Json
$dateData=$responseContent.$date
foreach($dataset in $dateData){
	$type=$dataset.type
	if($type -eq '壹句'){
		$oneLineSource=$dataset.source
		$oneLineSummary=$dataset.summary
		$oneLineTitle=$dataset.title
		$oneLine=""
		$oneLine=$oneLine+"-----------------------------------------------`n"
		$oneLine=$oneLine+"--Source: $oneLineSource`n"
		$oneLine=$oneLine+"--CN: $oneLineSummary`n"
		$oneLine=$oneLine+"--EN: $oneLineTitle`n"
		$oneLine=$oneLine+"-----------------------------------------------`n"
	} 
} 
Write-Host "$oneLine"
$page="{0:D}" -f (Get-Date).DayofYear
$uri = "http://www.iwmgh.com/api/call_db.php?cmd=getZhuji&page={0}&type=most&db_name=MnemonicDb&fun_name=UserRequestEntry&r=0.51078588689454544" -f $page
Write-Host "Call $uri"
$header = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$header.Add("Accept", "application/json")
$response = Invoke-WebRequest -Method Get -Uri $uri -header $header
# Check response status code
#stopScript -statusCode $response.StatusCode -message "Cannot list $resourceType."
#$responseContent = $($response.Content | ConvertFrom-Json).Value

$oneLine=$oneLine+"-----------------------------------------------`n"

$responseContent = $response.Content | ConvertFrom-Json
$dateData=$responseContent[1]
foreach($dataset in $dateData){
$oneLineWord=$dataset[1]
$oneLineTranslation=$dataset[2]
$oneLine=$oneLine+"--EN: $oneLineWord`n"
$oneLine=$oneLine+"--CN: $oneLineTranslation`n"
$oneLine=$oneLine+"-----------------------------------------------`n"
} 
Write-Host "$oneLine"


$file="C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql"
$fileTemp="XXXXX\SqlTemplate.sql" #预定义模板文件地址
#$fileContent=get-content -Path $fileTemp -Encoding UTF8 | select -Skip 5 | %{$PSItem+"`n"}
$fileContent=get-content -Path $fileTemp -Encoding UTF8 | select | %{$PSItem+"`n"}
#Write-Host "$fileContent"
$oneLine+$fileContent | set-content "$file"

将以上代码保存为一ps1 文件,更改 #预定义模板文件地址，然后在Task Scheduler中设置一个日常任务。然后每天就可以看到不同的一句。

最终效果

-----------------------------------------------
--Source: 
--CN: 唱歌本就该唱出你自己的个性。
--EN: You're supposed to sing like you.
-----------------------------------------------
-----------------------------------------------
--EN: kettle
--CN: _ttle 串烧助记：瓶子（bottle）易碎（brittle）因战斗（battle），小壶（kettle）虽小（little）安顿（settle）牲口（cattle）
-----------------------------------------------
--EN: commemorate
--CN: com-com表示"共同"。commemorate纪念(com+memor记住+ate→大家一起记住→纪念)
-----------------------------------------------
--EN: chain
--CN: cha 插 in 在里面
-----------------------------------------------
--EN: mutton
--CN: cotton棉 mutton绵羊
-----------------------------------------------
--EN: confront
--CN: front=forehead表示"前额"。confront v 面临，对抗（con 互相，共同+front→互相面对→对抗）
-----------------------------------------------

 --C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SqlWorkbenchProjectItems\Sql
 
    
    
  --以下是预定义模板内容 
 select DbRole = g.name, MemberName = u.name, MemberSID = u.sid  
 from sys.database_principals u, sys.database_principals g, sys.database_role_members m  
 where g.principal_id = m.role_principal_id  
 and u.principal_id = m.member_principal_id  
 and u.name like '%'
 order by 1, 2

------------------------------------------------------------------------------------------------------------------------------------------------

好久没动手了。

哈哈，好像要干架的感觉。

开始写。

当我们在SSMS中点击New Query 时，SSMS会打开一个查询窗口。而这个窗口中的内容我们可以通过模板文件进行预设置。

模板文件在以下路径中，不同的SQL SERVER版本路径也会有所不同，我得是2014, 路径如下。

C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\SQLFile.sql

如果你得是其他版本，可以试试以下路径，其中的100 对应SQL SERVER 2008，如果你是SQL SERVER 2012 那应是 110.

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql

如果我们更改SQLFile.sql中的内容，再点击New Query时，窗口中的内容就会更新。更改的过程可能会提示，要权限。由于这个文件目录是不允许修改的，所以我先删了SQLFile.sql这个文件，然后将新的SQLFile.sql放入这个文件夹。后面我做的时候，我预先修改了这个文件夹的访问权限，改为了Full Control。

好了说说我想干嘛，由于每天我会点击上百次New Query，所以我准备将爱词霸的每日一句放入这个模板文件，这样每天无形中我可以学习一句英文。

OK，talk is cheap，show me the code！

Sub CalendarPhrases()
    Dim iLocalfile, iRemote, i, j,ibackupFile
    Dim xPost, sGet,strContents


    'URL from website iciba, it can be different when you see this article
    url = "http://news.iciba.com/dailysentence/detail-"+cstr(1421+DateDiff("d", "2015-08-12", Now())*2)+".html?from=calendar"
    'add the calendar phrase as a comment in the SQLFile.sql
    'I use the regular expression to get the content.
    result = "/* "+cstr(now())+ VbCrlf
    result = result+ VbCrlf +RegMatchValue(GetTextFromURL(url), "<li class=""en""><a>[^>]*(?=</a><span)")
    result = Replace(result, "<li class=""en""><a>", "")
    result = result + VbCrlf +VbCrlf + RegMatchValue(GetTextFromURL(url), "<li class=""cn""><a>[^>]*(?=</a></li>)")+ VbCrlf +"*/"+vbCrLf+vbCrLf
    result = Replace(result, "<li class=""cn""><a>", "")
    'MsgBox result




    Dim File, FileObject
    'below file path can be different for different vesion of SQL SERVER
    iLocalfile = "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\SQLFile.sql"
    ibackupFile = "C:\Temp\"
    Set FileObject = CreateObject("scripting.FileSystemObject")
    'before I change the template sql, I back up it into a folder Temp.
    If FileObject.FileExists(iLocalfile) Then 
        FileObject.CopyFile iLocalfile,ibackupFile
    End If 




    Const ForReading = 1
    Set File = FileObject.OpenTextFile(iLocalfile, ForReading)
    'ignore first 7 lines,which will be used to put the calendar phrases
    For i = 1 To 7
        File.ReadLine
    Next


    Do Until File.AtEndOfStream
        strContents = strContents + vbCrLf + File.ReadLine
        
    Loop
    File.Close


    result = result + vbCrLf + strContents
    'MsgBox result




    'write it to the file, as I also keep the translation, so I use ADODB.Steam.
    Set sGet = CreateObject("ADODB.Stream")
    sGet.Mode = 3
    sGet.Type = 2
    sGet.Charset = "utf-8"
    sGet.Open
    sGet.WriteText result
    sGet.SaveToFile iLocalfile, 2
    sGet.flush
    sGet.Close
    Set sGet = Nothing




End Sub
'=================================================================================================
' ¸get the web text from url.
'=================================================================================================
Function GetTextFromURL(url)
    Dim Retrieval
    On Error Resume Next
    Set Retrieval = CreateObject("Microsoft.XMLHTTP")
    
    Retrieval.Open "GET", url, False, "", ""
    Retrieval.send
    
    GetTextFromURL = Retrieval.responsetext


    If Err.Number <> 0 Then
        'msgbox "There is some error1:"& err.number&"-"&err.Description
        Set Retrieval = Nothing
        Exit Function
    Else
        'msgbox "Successfully GetURL!"
    End If
End Function
'=================================================================================================
' ¸Get the information what we need.
'=================================================================================================
Function RegMatchValue(SourceString, PatternStr)
    Dim Reg, Match, Matches, RetStr
    Set Reg = New RegExp
    'Set Reg = CreateObject("vbscript.regexp")
    Reg.IgnoreCase = True
    Reg.Global = True
    Reg.MultiLine = False
    Reg.Pattern = PatternStr
    Set Matches = Reg.Execute(SourceString)
    For Each Match In Matches
        RetStr = RetStr & Match.Value
    Next
    RegMatchValue = RetStr
End Function




call  CalendarPhrases

将以上代码保存为一个vbs 文件，然后在Task Scheduler中设置一个日常任务。然后每天就可以看到不同的一句。

下面是我得到的。

/* 2015/8/12 12:11:49

If you want to achieve a high goal, you're going to have to take some chances.

 如果你想达到一个更高目标，你必须得冒点险。（阿尔贝托·萨拉查）
*/



SELECT Create_date,* FROM SYS.OBJECTS WHERE TYPE='P' AND NAME LIKE '%%'

SELECT OBJECT_NAME(SM.OBJECT_ID),DEFINITION FROM SYS.SQL_MODULES  SM
JOIN SYS.OBJECTS SO
ON SO.OBJECT_ID=SM.OBJECT_ID
WHERE SO.TYPE='P' AND DEFINITION LIKE ''

Good Luck

十林

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
VBS/ PowerShell 自动更新SQL SEVER Management Studio Query 模板

2019-08-15 更新添加一个用powershell 实现。更改SQL Server Management Studio 18.0的query 模板## Script1.ps1# C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SqlWorkbenchProjectItem...
复制链接

扫一扫