?
Solved

Stored Procedure to search all tables and fields

Posted on 2008-10-31
7
Medium Priority
?
451 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 2000 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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

752 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