Solved

Stored Procedure to search all tables and fields

Posted on 2008-10-31
7
445 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

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…
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 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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 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