If you've spent much time working with Dynamics GP and SQL Server, you've likely needed to search for a table, stored procedure, table column, or specific data value. Many times while tracing a problem with a 3rd party module, I've had to find a table, but didn't remember the table name or the database in which the object was stored.
There are several ways to search SQL Server for an object. Because I find it the simplest to remember, and think its results are the easiest to read, I have used the INFORMATION_SCHEMA views for many years. I typically know whether I'm searching for a table vs. stored procedure vs. field, so one the INFORMATION_SCHEMA views usually work fine for me.
Other people prefer to use sysobjects, now called sys.objects in the current versions of SQL Server. (sysobjects was deprecated in SQL 2005, and should be replaced with sys.objects) sys.objects is handy if you don't know the type of object you are looking for, or if you want to search for objects regardless of type.
If you frequently need to search for objects, a reader of SQL Server Magazine has contributed this handy script that allows you to search across all objects across all databases in your SQL instance based on a partial name.
A few times I have been in a situation where I needed to find all records in all tables in a database that contained a specific value, independent of field name. In those cases, it is possible to search all fields in all tables for a given value. It may take quite a while to search a large database and may return alot of data, so obviously it isn't something you want to do on a regular basis, but it can be a life saver when you need it.
No comments:
Post a Comment