@manhnguyenv

Welcome to my blog!

Search string in SQL Server

February 18, 2019 23:44

Search string in SQL Server (edit

select * from SysMenu_View where Menu_Text like N'%xuất%' OR Menu_Text_Me like N'%xuất%'

select * from SysMenu_View where Bac_Menu=1 AND Menu_Function IS NULL

select * from SysMenu_View where Bac_Menu=2 AND Menu_Function IS NOT NULL

--Phiếu xuất bán hàng
select * from SysMenu_View where ID = 62
--Chon = 0
--Id = 62
--Menu_Name = MnuCtHD
--Menu_Text = Phiếu xuất bán hàng
--Menu_Icon = MnuCT.png
--Ph_Icon = CTHD.png
--Stt_Order = 21
--Menu_Function = Acpi.Main.dll,Acpi.Main.SYSLIB._ChungTu('HD')
--Id_Menu_Me = 3
--Bac_Menu = 2
--Stt_Menu = Mnu03.021.MnuCtHD
--Ph_VBT = NULL
--Ph_MH = NULL
--Ph_Kho = NULL
--Ph_BH = 2
--Ph_TSCD = NULL
--Ph_CPTT = NULL
--Ph_TH = NULL
--Visible = 1
--Menu_Name_Me = Mnu03
--Menu_Text_Me = &Chứng từ
--Menu_Cuoi = 1
--Bold = 0

Search string in SQL Server

---- Nội dung: spSearchStringInTable
---- Chạy: spSearchStringInTable
---- Sử dụng kết quả trả về: select * from SysMenu where Menu_Text = N'Phiếu xuất bán hàng'

---- 1. Chạy: spSearchStringInTable

IF OBJECT_ID('TempDB..#Result', N'U') IS NOT NULL DROP TABLE #Result;
CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX), [COLUMN VALUE] NVARCHAR(MAX), [COLUMN Name] sysname, [TABLE SCHEMA] sysname, [TABLE Name] sysname)
DECLARE @Table_Name sysname, @SearchString NVARCHAR(MAX), @Table_Schema sysname
SET @SearchString = N'Phiếu xuất bán hàng'

DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT Table_Schema, Table_Name
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY Table_Schema, Table_Name

OPEN curAllTables
FETCH curAllTables
INTO @Table_Schema, @Table_Name
WHILE (@@FETCH_STATUS = 0) -- Loop through all tables in the database
BEGIN
INSERT #RESULT ([PK COLUMN], [Column Value], [Column Name], [Table Schema], [Table Name])
EXECUTE spSearchStringInTable @SearchString, @Table_Schema, @Table_Name

FETCH curAllTables
INTO @Table_Schema, @Table_Name
END -- while
CLOSE curAllTables
DEALLOCATE curAllTables
-- Return results
SELECT * FROM #RESULT ORDER BY [Table Name]

---- 2. Nội dung: spSearchStringInTable
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'spSearchStringInTable' AND ROUTINE_TYPE = 'PROCEDURE')
EXECUTE ('CREATE PROCEDURE dbo.spSearchStringInTable AS SET NOCOUNT ON;');
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spSearchStringInTable]
(@SearchString NVARCHAR(MAX),
@Table_Schema sysname = 'dbo',
@Table_Name sysname)
AS
BEGIN
DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)

-- Get all character columns
SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
ORDER BY COLUMN_NAME
FOR XML PATH('')),1,2,'');

IF @Columns IS NULL -- no character columns
RETURN -1;

-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
ORDER BY COLUMN_NAME
FOR XML PATH('')),1,2,'');

SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT '

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name
ORDER BY CU.ORDINAL_POSITION
FOR XML PATH('')),1,9,'');

IF @PkColumn IS NULL
SELECT @PkColumn = 'CAST(NULL AS nvarchar(max))';

-- set select statement using dynamic UNPIVOT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT *, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +
' FROM
(SELECT '+ @PkColumn + ' AS [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) + ' ) src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt
WHERE [Column Value] LIKE ''%'' + @SearchString + ''%'''

--print @SQL

EXECUTE sp_ExecuteSQL @SQL, N'@SearchString nvarchar(max)', @SearchString;
END

GO

Categories

Recent posts