May 30, 2007 at 2:53 pm
Hi All
I'm not too familiar with working with sys.objects etc.
I have an application running on SQl Server 2005. It accesses 10 different databases, and each has an average of 40-50 tables (it's an ERP system).
I need to write an update statement that updates all instances of a SiteID / SiteCode. All the columns that contain either a SiteID or SiteCode are named "SiteID", "SiteCode" or "Site_Code".
What I want is a query that will search all tables in all DB's and list the following:
Table Name
Column Name
DataType (e.g. varchar(2), int, etc)
Where the column name is in ('SiteID', 'SiteCode', 'Site_Code') or like 'Site%'
Can anyone help me out?
Thanks
mark
May 30, 2007 at 11:56 pm
Hi Mark,
Perhaps something along the lines of this would do the trick (you should double check it!);
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
'SIZE/PRECISION'= CASE DATA_TYPE
WHEN 'char' then CHARACTER_MAXIMUM_LENGTH
WHEN 'varchar' then CHARACTER_MAXIMUM_LENGTH
ELSE NUMERIC_PRECISION
END,
'SCALE'= CASE DATA_TYPE
WHEN 'numeric' then NUMERIC_SCALE
END
from INFORMATION_SCHEMA.COLUMNS
where
COLUMN_NAME in ('SiteID', 'SiteCode', 'Site_Code') or COLUMN_NAME like ''Site%'
order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
NB. Syntax will check all tables (User and System) but this should not be an issue given your selection criteria. Alternately, join in 'INFORMATION_SCHEMA.TABLES' to limit the selection to 'BASE TABLE'.
Finally, you could even create this as a UDF or SPR and use the SP_MSForeachDB to loop through each database.
Hope that this helps,
Duane
May 31, 2007 at 6:03 am
Hi Duane
I tried it and it just returned tables from system. If I add "use <db name>" it does one db at a time. Anyway to do all db's together?
Other than that: it work's great
Thanks for the help
Mark
June 6, 2007 at 10:02 am
Mark
Either (a) create a temp table, insert the results for each database into it and then select them all, or (b) qualify the view name thus: MyDB.INFORMATION_SCHEMA.COLUMNS and use UNION to get the info for all databases into one result set.
John
June 6, 2007 at 12:51 pm
Thnaks John
I'll give it a try
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply