时间戳

首页 > 解决方案 > 微软 SQL 。搜索选定数据库中的所有表，查找所有行/列中的关键字

sql - 微软 SQL 。搜索选定数据库中的所有表，查找所有行/列中的关键字

问题描述

我在 MS SQL 数据库中有大量表，我需要根据关键字运行搜索，查看每一行/列中是否有该关键字的任何实例。如果找到匹配项，则报告该表名称、列和行号。遍历所有以 Dev11 开头的表。因为我需要过滤表格，因为我不需要查看所有表格。我发现下面添加了一个 SQL 脚本，我想让它只查看带有文本的列，以及以 Dev1 开头的表工作表。但由于它将它保存到一个名为 #results 的表中，我不确定如何修改它以在 excel 中工作

Public Const ConnString = "Provider=SQLOLEDB.1;" & _
"Data Source=dev1;" & _
"Initial Catalog=Database1;" & _
"Password=pwd1;" & _
"Persist Security Info=True;" & _
"User ID=datauser;" & _
"Use Procedure for Prepare=1;" & _
"Auto Translate=True;" & _
"Packet Size=4096;" & _
"Workstation ID=123456;" & _
"Use Encryption for Data=False;" & _
"Tag with column collation when possible=False;"


Public connMasterConnection As New ADODB.Connection
Sub SQL_Lessor_All()

Dim conn As New ADODB.Connection
Dim connMasterConnection As New ADODB.Connection
Dim rsObj As New ADODB.Recordset
Dim Ws_Company As Worksheet
Dim strSQL As String
Dim search_string As String
Dim iCols As Integer
Dim varname1 As String

Set Ws_Company = ActiveWorkbook.Worksheets("Company")
Ws_Company.Cells.Clear
search_string = Sheet1.Range("B1").Value
If connMasterConnection.State = 0 Then connMasterConnection.Open ConnString

varname1 = ""
varname1 = varname1 & "DECLARE @SearchStr nvarchar(100)"


varname11 = ""
varname11 = varname11 & "SET @SearchStr = '" & search_string & "' " & vbCrLf
varname11 = varname11 & " " & vbCrLf
varname11 = varname11 & "CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))"


varname12 = ""
varname12 = varname12 & "SET NOCOUNT ON"


varname13 = ""
varname13 = varname13 & "DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)"


varname14 = ""
varname14 = varname14 & "SET  @TableName = ''"


varname15 = ""
varname15 = varname15 & "SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')"


varname16 = ""
varname16 = varname16 & "WHILE @TableName IS NOT NULL " & vbCrLf
varname16 = varname16 & " " & vbCrLf
varname16 = varname16 & "BEGIN " & vbCrLf
varname16 = varname16 & "SET @ColumnName = '' " & vbCrLf
varname16 = varname16 & "SET @TableName = " & vbCrLf
varname16 = varname16 & "( " & vbCrLf
varname16 = varname16 & "SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) " & vbCrLf
varname16 = varname16 & "FROM INFORMATION_SCHEMA.TABLES " & vbCrLf
varname16 = varname16 & "WHERE TABLE_TYPE = 'BASE TABLE' " & vbCrLf
varname16 = varname16 & "AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName " & vbCrLf
varname16 = varname16 & "AND OBJECTPROPERTY( " & vbCrLf
varname16 = varname16 & "OBJECT_ID( " & vbCrLf
varname16 = varname16 & "QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) " & vbCrLf
varname16 = varname16 & "), 'IsMSShipped' " & vbCrLf
varname16 = varname16 & ") = 0 " & vbCrLf
varname16 = varname16 & ") " & vbCrLf
varname16 = varname16 & " " & vbCrLf
varname16 = varname16 & "WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) " & vbCrLf
varname16 = varname16 & "" & vbCrLf
varname16 = varname16 & "BEGIN " & vbCrLf
varname16 = varname16 & "SET @ColumnName = " & vbCrLf
varname16 = varname16 & "( " & vbCrLf
varname16 = varname16 & "SELECT MIN(QUOTENAME(COLUMN_NAME)) " & vbCrLf
varname16 = varname16 & "FROM INFORMATION_SCHEMA.COLUMNS " & vbCrLf
varname16 = varname16 & "WHERE TABLE_SCHEMA    = PARSENAME(@TableName, 2) " & vbCrLf
varname16 = varname16 & "AND TABLE_NAME    = PARSENAME(@TableName, 1) " & vbCrLf
varname16 = varname16 & "AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal') " & vbCrLf
varname16 = varname16 & "AND    QUOTENAME(COLUMN_NAME) > @ColumnName " & vbCrLf
varname16 = varname16 & ") " & vbCrLf
varname16 = varname16 & " " & vbCrLf
varname16 = varname16 & "IF @ColumnName IS NOT NULL " & vbCrLf
varname16 = varname16 & " " & vbCrLf
varname16 = varname16 & "BEGIN " & vbCrLf
varname16 = varname16 & "INSERT INTO #Results " & vbCrLf
varname16 = varname16 & "EXEC " & vbCrLf
varname16 = varname16 & "( " & vbCrLf
varname16 = varname16 & "'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + " & vbCrLf
varname16 = varname16 & "' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 " & vbCrLf
varname16 = varname16 & ") " & vbCrLf
varname16 = varname16 & "END " & vbCrLf
varname16 = varname16 & "END " & vbCrLf
varname16 = varname16 & "END"

rsObj.Open varname1, connMasterConnection
For iCols = 0 To rsObj.Fields.Count - 1
Ws_Company.Cells(1, iCols + 1).Value = rsObj.Fields(iCols).Name
Next
Ws_Company.Range("A2").CopyFromRecordset rsObj
connMasterConnection.Close


End Sub

来自这个 SQL 脚本

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'China'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM     INFORMATION_SCHEMA.TABLES
WHERE         TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA    = PARSENAME(@TableName, 2)
AND    TABLE_NAME    = PARSENAME(@TableName, 1)
AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND    QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL

BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END  
END

SELECT ColumnName, ColumnValue FROM #Results

标签： sqlsql-serverexcelvba

解决方案

尝试使用正则表达式子句。

import mysql.Connector # Third Party
db = mysql.Connector.connect(user="userName", passwd="password", host="localhost", database="<database>")
cursor = db.cursor()
cursor.execute('''SELECT column FROM <'tableName'>
                  WHERE column REGEXP "keyword" ''')

print(cursor.fetchall())

推荐阅读