Tổng hợp tin game giải trí

Tổng hợp

Pass Parameters To A Sql Server Stored Procedures For Beginners

I want to search a text from all my database stored procedures. I use the below SQL:

SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like “%%”;I want to search for in all stored procedures including square brackets, but it”s not giving the proper result. How can I change my query to achieve this?

*

*

Escape the square brackets:

…WHERE m.definition Like “%%” ESCAPE “”Then the square brackets will be treated as a string literals not as wild cards.

Đang xem: Sql server stored procedures for beginners

*

Try this request:

Query

SELECT nameFROM sys.proceduresWHERE Object_definition(object_id) LIKE “%strHell%”

*

*

Have you tried using some of the third party tools to do the search? There are several available out there that are free and that saved me a ton of time in the past.

Below are two SSMS Addins I used with good success.

ApexSQL Search – Searches both schema and data in databases and has additional features such as dependency tracking and more…

SSMS Tools pack – Has same search functionality as previous one and several other cool features. Not free for SQL Server 2012 but still very affordable.

I know this answer is not 100% related to the questions (which was more specific) but hopefully others will find this useful.

I usually run the following to achieve that:

select distinct object_name(id) from syscomments where text like “%%”order by object_name(id)
Good practice to work with SQL Server.

Create below stored procedure and set short key,

CREATE PROCEDURE . (
strFind AS VARCHAR(MAX))ASBEGIN SET NOCOUNT ON; –TO FIND STRING IN ALL PROCEDURES BEGIN SELECT OBJECT_NAME(OBJECT_ID) SP_Name ,OBJECT_DEFINITION(OBJECT_ID) SP_Definition FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE “%”+
strFind+”%” END –TO FIND STRING IN ALL VIEWS BEGIN SELECT OBJECT_NAME(OBJECT_ID) View_Name ,OBJECT_DEFINITION(OBJECT_ID) View_Definition FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE “%”+
strFind+”%” END –TO FIND STRING IN ALL FUNCTION BEGIN SELECT ROUTINE_NAME Function_Name ,ROUTINE_DEFINITION Function_definition FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE “%”+
strFind+”%” AND ROUTINE_TYPE = “FUNCTION” ORDER BY ROUTINE_NAME END –TO FIND STRING IN ALL TABLES OF DATABASE. BEGIN SELECT t.name AS Table_Name ,c.name AS COLUMN_NAME FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE “%”+
strFind+”%” ORDER BY Table_Name ENDENDNow – Set short key as below,

*

So next time whenever you want to find a particular text in any of the four objects like Store procedure, Views, Functions and Tables. You just need to write that keyword and press shortcut key.

For example: I want to search “PaymentTable” then write “PaymentTable” and make sure you select or highlight the written keyword in query editor and press shortcut key ctrl+4 – it will provide you full result.

Xem thêm: Nghĩa Của Từ Speciality Là Gì, Speciality Là Gì, Nghĩa Của Từ Speciality

Please take this as a “dirty” alternative but this saved my behind many times especially when I was not familiar with the DB project. Sometimes you are trying to search for a string within all SPs and forget that some of the related logic may have been hiding between Functions and Triggers or it can be simply worded differently than you thought.

From your MSSMS you may right click your DB and selectTasks -> Generate Scripts wizard to output all the SPs, Fns and Triggers into a single .sql file.

*

Make sure to select Triggers too!

*

Then just use Sublime or Notepad to search for the string you need to find. I know this may be quite inefficient and paranoid approach but it works 🙂

You can also use this one:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION like “%Search_String%”
It might help you!

SELECT DISTINCT A.NAME AS OBJECT_NAME, A.TYPE_DESC FROM SYS.SQL_MODULES M INNER JOIN SYS.OBJECTS A ON M.OBJECT_ID = A.OBJECT_ID WHERE M.DEFINITION LIKE “%<"+
SELECT DISTINCT o.name AS Object_Name, o.type_descFROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like “%%”;
Also you can use:

SELECT OBJECT_NAME(id) FROM syscomments WHERE LIKE “%flags.%” AND OBJECTPROPERTY(id, “IsProcedure”) = 1 GROUP BY OBJECT_NAME(id)Thats include comments

SELECT DISTINCT OBJECT_NAME(), FROM syscomments WHERE IN (SELECT FROM sysobjects WHERE xtype IN (“TF”,”FN”,”V”,”P”) AND status >= 0) AND ( LIKE “%text to be search%” ) OBJECT_NAME() –> Object Name (View,Store Procedure,Scalar Function,Table function name)

id (int) = Object identification number

xtype char(2) Object type. Can be one of the following object types:

FN = Scalar function

P = Stored procedure

V = View

TF = Table function

I created a procedure to search text in procedures/functions, tables, views, or jobs. The first parameter
db is to specify the database to search, default to your current database. Here is my query in dynamic SQL.

ALTER PROCEDURE .(
target LIKE “”%Procedure%”” BEGIN SELECT o.name As “”Stored Procedures”” FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id WHERE c.text LIKE
target LIKE “”%View%”” BEGIN SELECT o.name As “”Views”” FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id WHERE c.text LIKE
search AND o.xtype = “”V”” GROUP BY o.name ORDER BY o.nameEND/* Table – search table name only, need to add column name */ELSE IF
target LIKE “”%Table%”” BEGIN SELECT t.name AS “”TableName”” FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE
target LIKE “”%Job%”” BEGIN SELECT j.job_id, s.srvname, j.name, js.step_id, js.command, j.enabled FROM .dbo.sysjobs j JOIN .dbo.sysjobsteps js ON js.job_id = j.job_id JOIN master.dbo.sysservers s ON s.srvid = j.originating_server_id WHERE js.command LIKE
searchENDELSE BEGIN SELECT o.name As “”Stored Procedures”” FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id WHERE c.text LIKE
search AND o.xtype IN (“”P””,””FN””) GROUP BY o.name ORDER BY o.name SELECT o.name As “”Views”” FROM SYSOBJECTS o INNER JOIN SYSCOMMENTS c ON o.id = c.id WHERE c.text LIKE
search AND o.xtype = “”V”” GROUP BY o.name ORDER BY o.name SELECT t.name AS “”Tables”” FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE
search ORDER BY Tables SELECT j.name AS “”Jobs”” FROM .dbo.sysjobs j JOIN .dbo.sysjobsteps js ON js.job_id = j.job_id JOIN master.dbo.sysservers s ON s.srvid = j.originating_server_id WHERE js.command LIKE
TSQL Update: If you renamed a procedure, it only updates sysobjects but not syscomments, which keeps the old name and therefore that procedure will not be included in the search result unless you drop and recreate the procedure.

Using CHARINDEX:

SELECT DISTINCT o.name AS Object_Name,o.type_descFROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_idWHERE CHARINDEX(““,m.definition) >0 ;Using PATINDEX:

SELECT DISTINCT o.name AS Object_Name,o.type_descFROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_idWHERE PATINDEX(“<<>ABD>”,m.definition) >0 ; Using this double <<>ABD> is similar to escaping :

WHERE m.definition LIKE “%<<>ABD>%”
also try this :

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION like “%%”
Select distinct OBJECT_NAME(id) from syscomments where text like “%string%” AND OBJECTPROPERTY(id, “IsProcedure”) = 1
T_Find_Text VARCHAR(1000) = “Foo”IF OBJECT_ID(“tempdb..#T_DBNAME”) IS NOT NULL DROP TABLE #T_DBNAMEIF OBJECT_ID(“tempdb..#T_PROCEDURE”) IS NOT NULL DROP TABLE #T_PROCEDURECREATE TABLE #T_DBNAME( IDX int IDENTITY(1,1) PRIMARY KEY , DBName VARCHAR(255))CREATE TABLE #T_PROCEDURE( IDX int IDENTITY(1,1) PRIMARY KEY , DBName VARCHAR(255) , Procedure_Name VARCHAR(MAX) , Procedure_Description VARCHAR(MAX))INSERT INTO #T_DBNAME (DBName)SELECT name FROM master.dbo.sysdatabasesDECLARE
/* SEARCH SPROCS & VIEWS The following query will allow search within the definitions of stored procedures and views. It spits out the results as XML, with the full definitions, so you can browse them without having to script them individually.*//* STEP 1: POPULATE SEARCH KEYS. (Set to NULL to ignore)*/DECLARE
def_key varchar(128) = “%foo%”, /* root element */SELECT ( /* This inner query maps stored procedure rows to elements */ SELECT TOP 100 PERCENT AS <
full_name>, AS FROM SearchResults WHERE object_type = “P” ORDER BY , FOR XML PATH (“procedure”), TYPE) AS , /* elements to be wrapped within the element */( /* This inner query maps view rows to elements */ SELECT TOP 100 PERCENT AS <
full_name>, AS FROM SearchResults WHERE object_type = “V” ORDER BY , FOR XML PATH (“view”), TYPE) AS /* elements to be wrapped within the element */FOR XML PATH (“sql_objects”)
Every so often I use this script to figure out which procs to modify, or to figure out what uses a column of a table, or that table at all to remove some old junk. It checks each database on the instance it is ran on by the wonderfully supplied sp_msforeachdb.

Xem thêm: Sau Seem Là Gì – Cách Dùng Seem, Sau Seem Dùng Gì

if object_id(“tempdb..##nothing”) is not null drop table ##nothingCREATE TABLE ##nothing( DatabaseName varchar(30), SchemaName varchar(30), ObjectName varchar(100), ObjectType varchar(50))EXEC master.sys.sp_msforeachdb “USE ?insert into ##nothingSELECT db_name() AS ,=schema_name(o.schema_id), o.Name, o.type FROM sys.sql_modules mINNER JOIN sys.objects o ON o.object_id = m.object_idWHERE m.definition like “”%SOME_TEXT%””” –edit this textSELECT * FROM ##nothing norder by OBJECTname
— Applicable for SQL 2005+USE YOUR_DATABASE_NAME //; GOSELECT = schema_name(o.schema_id) ,o.NAME ,o.typeFROM sys.sql_modules mINNER JOIN sys.objects o ON o.object_id = m.object_idWHERE m.DEFINITION LIKE “%YOUR SEARCH KEYWORDS%”GO
A different version, To make query more appropriate for different coding practices.

SELECT DISTINCT O.NAME AS OBJECT_NAME, O.TYPE_DESC FROM SYS.SQL_MODULES M INNER JOIN SYS.OBJECTS O ON M.OBJECT_ID = O.OBJECT_ID WHERE UPPER(M.DEFINITION) LIKE UPPER(“%Your Text%”);
Highly active question. Earn 10 reputation in order to answer this question. The reputation requirement helps protect this question from spam and non-answer activity.

Not the answer you're looking for? Browse other questions tagged sql sql-server stored-procedures or ask your own question.

site design / logo © 2021 Stack Exchange Inc; user contributions licensed under cc by-sa. rev2021.5.10.39252

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Trả lời