June 10, 2008 at 1:47 am
can anyone help me a script to find a defined attribute in given database. say if i want to find attribute 'name' in database, then after running the script it shuld display all the table names which contain the attribute name. Actually i have joined the team which is already working on a database of almost 100 tabls, for which they hired outside team to develop. so we have no database diagrams, and connection diagram. not even the s./w code of front-end , we have only .exe file.even after developing dfd in visio or sql, there are some dependencies/ values which go to multiple tables but are not shown connected in database schema, i have to study the databae in deapth as early as possible..
i expect some Database expert to help me soon.
June 10, 2008 at 8:23 am
there are catalog views that you can use to browse table/column/datatype
- you should use INFORMATION_SCHEMA.* views, but example below deviates to use tables
you might assume that MS has a "clean" definition for AdventureWorks - not so!
try this TSQL in SSMS (written for SQL2005)
-- show some datatype variance within the flagship AdventureWorks db !
use AdventureWorks-- downloadable from http://codeplex.com/SqlServerSamples
go
selectTblName=schema_name(O.schema_id)+'.'+O.name
,ColName=C.name
,datatype=T.name +
case
when C.user_type_id > 255
then ''
when T.name like 'n%char'
then '('+convert(varchar,C.max_length/2)+')'
when T.name like '%char' or T.name like '%binary'
then '('+convert(varchar,C.max_length)+')'
when T.namein ('decimal','numeric')--isnull(C.precision,0)+isnull(C.scale,0)>0
then '('+convert(varchar,C.precision)+','+convert(varchar,C.scale)+')'
else ''
end
--, C.precision,C.scale,C.max_length
from sys.columns C
join sys.objects O on O.object_id=C.object_id
join sys.types T on T.user_type_id =C.user_type_id-- T.system_type_id =C.system_type_id
where is_ms_shipped=0
and C.name in ('City','FirstName','LastName','PostalCode','Title')
--order by TblName, ColName
order by ColName, datatype, TblName
showing that MS have defined some UDTs and not used them
dbo.ufnGetContactInformation.FirstName
dbo.ufnGetContactInformation.LastName
and have a confusing definition for other fields
City
PostalCode
Title
and I expect you will find lots more funnies in YOUR databases !
HTH
Dick
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply