Solved

Stored Procedure to search all tables and fields

Posted on 2008-10-31
7
443 Views
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
0
Comment
Question by:schmir1
  • 5
  • 2
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
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 '#%'
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22851645
0
 

Author Comment

by:schmir1
ID: 22852284
I should have said field data starting with #.  I'll check out the link
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22852318
just change the line that does:

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

to

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

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

Expert Comment

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

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

Author Comment

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

Expert Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

21 Experts available now in Live!

Get 1:1 Help Now