Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jp2code
Posting Yak Master
175 Posts |
 Posted - 2011-04-01 : 11:37:53
|
Our SQL 2000 Server has 18 databases on it, including the basic stuff like Northwind.During the 5-years I've been with this company, all of my development work in Visual Studio w/ C# has been focused on one of these databases - and I started by looking at someone else's code that basically told me which tables to pull from.Now, I'm having to venture out into one of the other databases. This database is populated by a vendor's software. I need to look up the serial number and print out work order information, etc. I do not need to write to it.The problem is, this database (like the one I've been developing on for years) has many tables in it (92 actually).Is there a query I can write that basically says, "Hey, which of these tables is seeing activity?"It'd really take a long time for me to weed through 92 tables looking for something that resembles one of these serial numbers!~Joe
Avoid Sears Home Improvement (read why) |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-01 : 11:52:12
|
quote:
The problem is, this database (like the one I've been developing on for years) has many tables in it (92 actually).
Don't ever work with ERP software, almost all of them start with over 1000 tables.  And definitely don't use SAP.If you're using the GUI to look for these columns, stop. Use the INFORMATION_SCHEMA views to find the information:SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE LIKE '%char' AND CHARACTER_MAXIMUM_LENGTH=15That will find you all the columns defined as char, nchar, varchar or nvarchar that have a maximum defined length of 15. If your serial numbers are longer or shorter, change the query accordingly. If they are integer or another numeric type (pray they aren't):SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('int', 'numeric', 'decimal') |
 |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2011-04-01 : 16:03:13
|
Sorry it took me so long to get back to you.This seems to pull up lots of stuff. Is there a way to look at records based on when they were last modified, or would the db designer have had to included some functionality to do that?That first query above didn't pull up anything at first. I changed the defined length to 50, and I think I got every defined column in the database!~Joe
Avoid Sears Home Improvement (read why) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-01 : 19:22:06
|
| You could filter by column name:SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE LIKE '%char' AND COLUMN_NAME LIKE '%serial%'INFORMATION_SCHEMA doesn't have last modified times, by sys.objects does, and maybe sys.tables. |
|
|
|
|
|
|
|
|
Help Learning A DB Design