Solved

Stored Procedure to search all tables and fields

Posted on 2008-10-31
7
447 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
[X]
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
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

740 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