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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

726 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