February 7, 2005 at 1:03 pm
Does anybody have a script to list all user created objects?
For a specific db or all db's.
Just want to know if there is any user created objects in my server without going into EM and open each db an check Store procedures, views, tables, etc.
Thanks for the help...
February 7, 2005 at 1:52 pm
I bet you'll find something here in the script section. As a very basic starter might this serve:
sp_msforeachdb "select * from sysobjects where objectproperty(id,'ismsshipped')=0"
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 8, 2005 at 7:30 am
Do you mean all user-created objects including dbo, or just all of the non-dbo objects? If you only want non-dbo objects, you could take Frank's idea and do start with the following code (uses dynamic SQL, but this is one of those cases where I believe it's safe):
DECLARE @sql varchar(8000)
SET @sql = ''
SELECT @sql = @sql +
'USE ' + CONVERT(varchar(25), name) + ' '
-- + ' PRINT ''' + CONVERT(varchar(25), name) + ''''
+ ' SELECT ''' + CONVERT(varchar(25), name) + ''' as dbname, id, uid, xtype, type, name from sysobjects where objectproperty(id,''ismsshipped'')=0 and uid > 1 order by type, xtype, name '
FROM master.dbo.sysdatabases
IF Datalength(@sql) = 8000
PRINT 'Aborting - cannot fit required statements into the @sql variable, string length exceeded.'
ELSE
EXEC (@sql)
February 8, 2005 at 10:13 am
Thank you guys for your help.
both ways did the trick.
I just wanted a quick way to list non-dbo objects on the db's.
Just trying to monitor db's and help with some auditing process.
Thanks again!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply