InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:01:23 GMT20http://www.sqlservercentral.com/Forums/Topic484782-146-1.aspxHi,You may use these two ways:1) Build dynamic query:SET NOCOUNT ONDECLARE @db NVARCHAR (50), @sqlStr NVARCHAR(4000)DECLARE @tbl_dbs TABLE (dbName NVARCHAR(50))INSERT @tbl_dbs SELECT Name from master.sys.databasesWHILE EXISTS (SELECT dbName FROM @tbl_dbs)BEGINSELECT TOP 1 @db = dbName, @sqlStr = 'INSERT ABC..YourTable (ObjectName, name,type,type_desc ) SELECT schema_name(schema_id)as ObjectName, name,type,type_desc from ' + @db + '.sys.objectswhere schema_name(schema_id) <> ''dbo''and type in(''U'',''P'',''V'', ''FN'',''TF'',''IF'',''PK'',''UQ'')' FROM @tbl_dbsEXEC master.dbo.sp_executesql @sqlStrDELETE FROM @tbl_dbsWHERE dbName = @dbEND2) Use undocumented stored procedure (which is not actually recommended):EXEC sp_MSforeachDB 'INSERT ABC..YourTable (ObjectName, name,type,type_desc ) SELECT schema_name(schema_id)as ObjectName, name,type,type_desc from ?.sys.objectswhere schema_name(schema_id) <> ''dbo''and type in(''U'',''P'',''V'', ''FN'',''TF'',''IF'',''PK'',''UQ'')'Tue, 15 Apr 2008 19:40:28 GMTmagasvshttp://www.sqlservercentral.com/Forums/Topic484782-146-1.aspxAlso I would need couple of details ,could you help me---This is what I needed,1)Objects which do not belong to DBOThis is the query we are using to find outselect schema_name(schema_id)as ObjectName, name,type,type_desc from sys.objectswhere schema_name(schema_id) <> 'dbo'and type in('U','P','V', 'FN','TF','IF','PK','UQ')go2)Database users which do not have DBO as their default schemaSELECT *FROM sys.database_principalsWHERE [type] IN ('U','S') AND ISNULL(default_schema_name,'')<>'dbo'We would like to create a report out of it and let a procedure run in every instance on a daily basis. The procedures should write the information into seperate tables in theOne of DB(Like ABC)databaseI need to have the Sp in one Database and it needs to check all the Database and fetch the details into separate tables as per the database nameCould you please help meTue, 15 Apr 2008 00:26:55 GMTsps-554505