Web design and hosting, database, cloud and social media solutions that deliver real business results
  • Business Solutions
    • Robotic Process Automation
    • Bespoke Software
    • Database Services
      • Data Integration
      • Datawarehouse Services
      • Power BI
    • Web Services
      • Logo Design
      • Payment Gateways
      • Web Site Optimisation
      • Web Site Security
      • Technical Tools
    • Cloud Services
      • Amazon Web Services
      • Google Cloud Services
      • Microsoft Azure
    • Microsoft Office
    • Social Media Services
  • Academy
    • Our Test Environment
    • Learning Databases
      • The Basics
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
      • Technical Tools
    • Learning Web Design
      • Building Ousia Content Management System
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Learning Cloud and IT Services
      • Task Scheduler Error 2147943645
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About
    • Blog
      • Building an entry level gaming machine
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
      • Top 5 websites for free Vector Graphics
    • Portfolio
    • Team
      • Adrian Anandan
      • Ali Al Amine
      • Ayse Hur
      • Chester Copperpot
      • Fernando Ancona Camara
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
      • Trevor Momanyi

SQL Server search for text in stored procedures and functions

SQL Server search for text in stored procedures and functions and get the line numbers of each row

About

A fairly simple bit of code, this can be used to pick up references and line numbers for certain key words within stored procedures and functions in your database.

It utilises the a function created in another article (link below), which splits strings into a table dependent on the input character of your choice. As we are passing in the return character to the function it means we can then drill down on what line the reference is made in.

More: SQL Server function splitting text into data rows

SQL

DECLARE @SearchStr NVARCHAR(100)='Holidays'
SELECT OBJECT_NAME(object_id) ObjectName,object_id ObjectID,WordInt as Line,
REPLACE(REPLACE(REPLACE(REPLACE(WordStr,CHAR(13)+CHAR(10),''),CHAR(10),''),CHAR(13),''),CHAR(9),'') as LineText
FROM [utilities].sys.all_sql_modules
OUTER APPLY [utilities].dbo.TextToRows(CHAR(13),definition) ttr
WHERE definition like '%'+@SearchStr+'%' AND
WordStr like '%'+@SearchStr+'%'

This will bring back the following results from our database. They are three functions that do various calculations with dates excluding holidays and can be found in the SQL Functions section.

More: SQL Server Functions

Results

                                     
ObjectNameObjectIDLineLineText
CalcWorkDaysBetween10101026399IF (DATEPART(WEEKDAY,@Date)IN(1,7) OR (SELECT Count(*) FROM Holidays WHERE Date=@Date)=1)
CalcWorkDaysMonth10261026969IF (DATEPART(WEEKDAY,@Date) IN(1,7)OR(SELECT Count(*) FROM Holidays WHERE Date=@Date)=1)
CalcWorkDaysAddDays104210275310IF NOT (DATEPART(WEEKDAY,@Date) IN (1,7) OR EXISTS (SELECT * FROM Holidays WHERE Date=@Date))

Author

Gavin Clayton
Gavin Clayton
I formed Claytabase in 2010 as a way of carrying on my work with SQL Server and ASP.NET. This has culminated in the Ousia Content Management System being taken from concept to one of the quickest CMS's on the market.

Was this helpful?

Please note, this commenting system is still in final testing.
Copyright Claytabase Ltd 2021, registered in England and Wales 08985867

Site Links

RSS Login ContactCookie PolicySitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.co.ukGround Floor, Building 1000, Lakeside North Harbour, Western Road, Portsmouth, Hampshire, United Kingdom, PO6 3EZ

Partnered With

Partners
The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Ousia CMS Loader