April 15, 2008 at 12:26 am
Also I would need couple of details ,could you help me
---
This is what I needed,
1)Objects which do not belong to DBO
This is the query we are using to find out
select schema_name(schema_id)as ObjectName, name,type,type_desc from sys.objects
where schema_name(schema_id) <> 'dbo'
and type in('U','P','V', 'FN','TF','IF','PK','UQ')
go
2)Database users which do not have DBO as their default schema
SELECT *
FROM sys.database_principals
WHERE [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)database
I 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 name
Could you please help me
April 15, 2008 at 7:40 pm
Hi,
You may use these two ways:
1) Build dynamic query:
SET NOCOUNT ON
DECLARE @db NVARCHAR (50), @sqlStr NVARCHAR(4000)
DECLARE @tbl_dbs TABLE (dbName NVARCHAR(50))
INSERT @tbl_dbs SELECT Name from master.sys.databases
WHILE EXISTS
(SELECT dbName FROM @tbl_dbs)
BEGIN
SELECT 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.objects
where schema_name(schema_id) <> ''dbo''
and type in(''U'',''P'',''V'', ''FN'',''TF'',''IF'',''PK'',''UQ'')'
FROM @tbl_dbs
EXEC master.dbo.sp_executesql @sqlStr
DELETE FROM @tbl_dbs
WHERE dbName = @db
END
2) 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.objects
where schema_name(schema_id) <> ''dbo''
and type in(''U'',''P'',''V'', ''FN'',''TF'',''IF'',''PK'',''UQ'')'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply