SQL Server T-SQL User-Defined Function of the Week

Which Tables are not Owned by DBO?

Volume 1, Number 1, December 2, 2002

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Visit http://www.NovickSoftware.com for additional material
about SQL Server, Visual Basic, ASP, XML, and security.

Visit the T-SQL UDF of the Week Archives at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeekArchive.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com


Many developers and DBA's consider it a "Best Practice" to have all
database objects owned by dbo.  This avoids the confusion that can 
occur when there are multiple objects with the same name owned by 
different users.

This week's UDF is udf_SQL_Tbl_NotOwnedByDBOTAB. It's an INLINE UDF
that returns a table listing all the tables that are not owned by dbo.  
Here's the CREATE FUNCTION script:

/------------- Copy From Below this line ----------------------\

CREATE  FUNCTION udf_SQL_Tbl_NotOwnedByDBOTAB ()

    RETURNS TABLE 

/* Returns a table of tables not owned by DBO
*
* Common Usage:  
select * from udf_SQL_Tbl_NotOwnedByDBOTAB()
*
* © Copyright 2002 Andrew Novick  www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
***************************************************************/

AS RETURN
    SELECT TOP 100 PERCENT WITH TIES 
           TABLE_SCHEMA as [Owner]
         , TABLE_NAME as [Name]
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
          AND TABLE_SCHEMA != 'dbo'
        ORDER BY [Owner], [Name]
GO

GRANT SELECT ON udf_SQL_Tbl_NotOwnedByDBOTAB TO PUBLIC
GO
\------------ Stop copying above this line --------------------/

To run it, use SQL Query Analyzer to execute the query:

/------------- Copy From Below this line ----------------------\
select * from udf_SQL_Tbl_NotOwnedByDBOTAB()
GO
\------------ Stop copying above this line --------------------/


udf_SQL_Tbl_NotOwnedByDBOTAB does have limitations.  For instance, 
it only works on tables.  It's also limited to working in the
database where it's defined so it has to be defined in every database  
where you want to run it.  Maybe someday I'll turn it into a system
UDF so it can run in any database.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Please send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try 
and respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+

This newsletter is published by Novick Software 
http://NovickSoftware.com
Copyright (c) 2002 Novick Software. All rights reserved.