Searching a windows explorer folder set of .sql files for a keyword using t-sql

Question

Searching a windows explorer folder set of .sql files for a keyword using t-sql

Weegee2017

SSC Eights!

Points: 994
More actions
January 25, 2018 at 10:56 am

#328262

hi there
I have a folder of .sql scripts, in a windows path 'E:\DeploymentScripts'.
I want to be able to search inside the code on all scripts for a keyword say for example 'StoredProcedureTest'
Does anyone know how i can do this in t-sql?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 1

Hi,

If that is not in a database you can't use t-sql. Because your files on disk you can use windows explorer advanced search with file content enabled - https://www.lifehacker.com.au/2015/09/set-windows-10-to-search-all-file-contents-with-this-setting/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Or use a tool like FileSeek, which is very useful if you have all of your .sql files stored locally in a VCS.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Weegee2017 - Thursday, January 25, 2018 10:56 AM
hi there
I have a folder of .sql scripts, in a windows path 'E:\DeploymentScripts'.
I want to be able to search inside the code on all scripts for a keyword say for example 'StoredProcedureTest'
Does anyone know how i can do this in t-sql?

Contrary to what has been stated, this can be done in SQL Server. The question is, do you really need to do the search from SQL Server or other the suggestions from the others OK for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Torre SSChampion Points: 10248 More actions · Answer 4

Weegee2017 - Thursday, January 25, 2018 10:56 AM
hi there
I have a folder of .sql scripts, in a windows path 'E:\DeploymentScripts'.
I want to be able to search inside the code on all scripts for a keyword say for example 'StoredProcedureTest'
Does anyone know how i can do this in t-sql?

Look at sys.xp_dirtree


DECLARE @tbl table
      (
      Id int IDENTITY
            PRIMARY KEY
   , [Dir-File] varchar(255)
   , Depth tinyint
   , IsFile bit
      );
INSERT @tbl
      (
      [Dir-File]
   , Depth
   , IsFile
      )
      EXEC sys.xp_dirtree
         'E:\DeploymentScripts'
      , 0
      , 1;
SELECT DISTINCT
      'E:\DeploymentScripts' + t1.[Dir-File] + '\' + t2.[Dir-File] + '\' + t3.[Dir-File] Files
FROM
      @tbl t1
JOIN @tbl t2 ON t2.Depth > t1.Depth
                  AND t2.Id > t1.Id
JOIN @tbl t3 ON t3.Depth > t1.Depth
                  AND t3.Depth > t2.Depth
                  AND t3.Id > t1.Id
                  AND t3.Id > t2.Id

ORDER BY
'\E:\DeploymentScripts\' + t1.[Dir-File] + '\' + t2.[Dir-File] + '\' + t3.[Dir-File];

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Jeff Moden - Thursday, January 25, 2018 2:02 PM
Weegee2017 - Thursday, January 25, 2018 10:56 AM
hi there
I have a folder of .sql scripts, in a windows path 'E:\DeploymentScripts'.
I want to be able to search inside the code on all scripts for a keyword say for example 'StoredProcedureTest'
Does anyone know how i can do this in t-sql?
Contrary to what has been stated, this can be done in SQL Server. The question is, do you really need to do the search from SQL Server or other the suggestions from the others OK for you?

FINDSTR via XP_CMDSHELL 😉

Far away is close at hand in the images of elsewhere.
Anon.

Weegee2017 SSC Eights! Points: 994 More actions · Answer 6

Phil Parkin - Thursday, January 25, 2018 1:42 PM
Or use a tool like FileSeek, which is very useful if you have all of your .sql files stored locally in a VCS.

cheers Phil

FileSeek does exactly what I needed.