April 15, 2008 at 5:15 am
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 6:25 am
Quick & dirty, this will run your proc on all databases within a given instance:
sp_msforeachdb 'use ?;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'',''K'',''UQ'')'
However, to gather up everything and put the output into another table, you might be better off, for a maintenance script of this type, generating a cursor of your databases on a given instance and then walking through the databases, and use your SELECT statement as part of an insert query into this maintenance database that you were talking about.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 15, 2008 at 7:41 am
hi,
Thanks ,this is great helpfull
Iam not having much exp on curson,could you please give a script
April 15, 2008 at 7:53 am
Some like this would work. Instead of just the SELECT statement, use that statement with an INSERT statement into the auditing table of choice.
DECLARE @SqlString NVARCHAR(MAX)
DECLARE @DbName NVARCHAR(MAX)
DECLARE MyDatabases CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT NAME FROM sys.databases
OPEN MyDatabases
FETCH NEXT FROM MyDatabases INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlString = 'use ' + @DbName + '; 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'',''K'',''UQ'')'
EXEC sp_executesql @SqlString
FETCH NEXT FROM MyDatabases INTO @DbName
END
CLOSE MyDatabases
DEALLOCATE MyDatabases
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2008 at 3:05 am
Thanks ,really helped this
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply