Help with query - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with query

Author

Topic

jubinjose
Starting Member

20 Posts

Posted - 2006-04-13 : 14:57:56

All tables in my database has primarykey field named as
[tablename]_key
All tables have fields [tablename]_create_date and [tablename]_end_date also along with other fields

For each column in every table I need to write userdefined function which goes like
udf_fn_[fieldname](@key,@create_date,@end_date) which returns value of the field.

Can someone help me with a script which I can run to create the user defined functions automatically for all user defined tables in my database

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-13 : 15:38:06

I don't know the effectiveness / efficiency, or any drawbacks of the following method.

I can give u some hints:
-- U may need to use Dynamic SQL
-- Get all table names that u want to do this
-- Then define a varchar and Set the Static String parts like 'udf_fn_' , '(@key,@create_date,@end_date)' , ... concatenated with Table names etc
-- If u want the body of the Function also to be written use the same technique, and use char(13) as the line break
-- Each Function containing text is assigned to a varchar and Use Execute to create the function
-- Loop thru to do the same for all tables

Srinika

esermeh
Starting Member

3 Posts

Posted - 2006-04-14 : 04:03:58

you may want to get the information from catalog views for table names or column names for each table.

jubinjose
Starting Member

20 Posts

Posted - 2006-04-14 : 05:00:36

Thanks for the responses. Though the definition is not exactly as I stated in my first post, I got the idea right. Output the following query results to text and execute the results is what I did

select 'create Function dbo.fn_' + column_name + '(@key int ,@date datetime)' + char(13) + 'Returns ' + data_type + char(13)
+ 'AS' + char(13) + 'BEGIN' + char(13) +
'DECLARE @' + column_name + ' ' + data_type + char(13) +
'select @' + column_name + ' = ' + column_name + ' from ' + table_name + char(13) +
' where ' +
table_name + '_key=@key' + char(13) + 'return @' + column_name +
char(13) + 'end' + char(13) + 'GO' + char(13) from information_schema.columns
where column_name <> table_name +'_key' and table_name like 'tbl_%'

I will need to write a similar function for columns of type varchar also (coz of the need to specify size in brackets for such variables)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources