Stored Procedure to search all tables and fields

Posted on 2008-10-31
Last Modified: 2012-05-05
I'm using SQL 2005 and I need a stored procedure to help me locate all tables and field names.  The procedures should do the following:
  Search all fields in the table for fields that begin with #
  Return the table name and all the field names beginning with #
  Do for each each table in the database
Question by:schmir1
  • 5
  • 2
LVL 39

Expert Comment

ID: 22851631
Field NAMES that begin with # or field data that begins with #?

If the first:
select * from inforamtion_schema.columns
where column_name like '#%'
LVL 39

Accepted Solution

BrandonGalderisi earned 500 total points
ID: 22851645

Author Comment

ID: 22852284
I should have said field data starting with #.  I'll check out the link
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 39

Expert Comment

ID: 22852318
just change the line that does:

QUOTENAME('%' + @SearchStr + '%','''')


QUOTENAME(@SearchStr + '%','''')

That way you are looking for it at the beginning only.
LVL 39

Expert Comment

ID: 22852323
"I should have said field data starting with #. "

It helps, but I gave you an answer in case it was either! :)

Author Comment

ID: 22909035
Thanks for your help.  It worked great but returned 1/2 Million records.  Took a while to go through.
LVL 39

Expert Comment

ID: 22909102
No doubt.  There is no EASY way to do that.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Caste datetime 2 51
SELECT * FROM table WHERE keyword…  (3 SQL queries needed) 10 84
How does this SELECT query work 11 100
Running Total in Access 4 0
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With  eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now