User defined functions
-
I have just read about user defined functions. I am trying to imagine what functions I might want to create. This is my list:
- Return the number of duplicate rows in a specified table
- List of tables that contain the word 'NULL' in some cells instead of the real <NULL>
- List of tables that are referred to in the stored procedures and are in the database
- List of tables that are referred to in the stored procedures and are not in the database
- Sub grouping of tables by column names found in all tables
- Number of rows in each of the tables in a database
- A comparison of a list of tables between database A and database B
- A list of base tables that do not have a primary key as an identity column
- Functions that will take a table name, a primary key column name and a number and then delete the oldest rows in that table (last rows in that table) according to the number of rows stated.
- Functions that will take a template table name, take a delimited flat file name and location, take a new table name and then use that info to create a new table with column names and column data types already present.
Do these seem reasonable. None of the items can be used within a select statement so would there be any point in having them as functions?
One exception. If I wanted to suppress data, which did not reach a critical value, could I then call a function to do this suppression for me? I could then set the suppression level depending on who was retrieving the data.
Does anyone have prewritten functions for any of these tasks and which on my list cannot be realistically created?
- Return the number of duplicate rows in a specified table
-
As you say, there's no great adavatage to using a function for much of this stuff as it won't be used in a SQL statement. In other cases, there are more specific points which apply:
1. a UDF can't have side effects, so updates inserts deletes and DDL aren't allowed.
2. a UDF can't use dynamic SQL, so you won't be able to write code which determines DDL of target objects at runtime in order to generate SQL.
- Return the number of duplicate rows in a specified table
see point 2.
- List of tables that contain the word 'NULL' in some cells instead of the real <NULL>
see point 2 - since you would need to find out which character columns were in the table in order to generate the SQL.
- List of tables that are referred to in the stored procedures and are in the database
Hard to do as sysdepends is not (ahem) dependable and you would have to use
select *
from syscomments c
left join syscomments c2
on c.id = c2.id
and c.colid = c2.id + 1
where isnull(c2.text,'') + c1.text like '% + @tabname + '%'
But this isn't reliable since the string could be quoted or commented or part of another string rather than a refrence to the specified table.
- List of tables that are referred to in the stored procedures and are not in the database
As above
- Sub grouping of tables by column names found in all tables
sysobjects and syscolumns have this info.
- Number of rows in each of the tables in a database
See point 2
- A comparison of a list of tables between database A and database B
See point 2 - if the names of the DBS are to be specified as arguments to the function
- A list of base tables that do not have a primary key as an identity column
sysconstraints, syscolumns should do it I think.
- Functions that will take a table name, a primary key column name and a number and then delete the oldest rows in that table (last rows in that table) according to the number of rows stated.
See points 1 and 2
- Functions that will take a template table name, take a delimited flat file name and location, take a new table name and then use that info to create a new table with column names and column data types already present.
See points 1 and 2
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Wow, Mark... that's quite the laudry list...
There are many solutions (and caveats as Tim pointed out) to your list of nice-to-haves... if you do a search on this site, I'm pretty sure you'll find most of them. This forum doesn't have a real advanced search but you'll get the hang of it in short order.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply