0

Using third party software that monitors queries in a Microsoft SQL server database, the following query seems to be executed more 70000 times per day!

select  
TABLE_QUALIFIER = convert(sysname,db_name()),  
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),  
TABLE_NAME = convert(sysname,o.name),  
TABLE_TYPE = convert(varchar(32),  
rtrim(substring('SYSTEM TABLE            TABLE       VIEW       ',  
(ascii(o.type)-83)*12+1,  
12)) -- 'S'=0,'U'=2,'V'=3

),  
REMARKS = convert(varchar(254),null) -- Remarks are NULL.

from  
sys.all_objects o  
where  
o.type in ('S','U','V') and  
has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' +     quotename(o.name),  
'object',  
'select') = 1 and  
charindex(substring(o.type,1,1),@type1) <> 0 and -- Only desired types.

(@table_name is NULL or o.name like @table_name) and  
(@table_owner is NULL or schema_name(o.schema_id) like @table_owner)  
order by 4,  
1,  
2,  
3

Does anyone know What is the purpose of this?

jenny
  • 933
  • 2
  • 11
  • 26

2 Answers2

1

Looking at the output, it seems to be ascertaining the objects for the database it is run against. It is getting the database name, the schema, the owner, the object name, then it converts that to show either a system table, a table or a view.

It looks like some variables are being passed into this, so it is more than likely querying specific objects.

Is this coming from an application, if so, could it be part of a security model? Can you trace if there are any consistent queries run before, or after this query to see if you can see what triggers it?

Leonidas199x
  • 133
  • 9
0

Using the program ExpressProfiler, I traced that the above query was in fact a system stored procedure in the Miscrosoft SQL Server database of the portal with the name sys.sp_tables. For some reason it is automatically called by the portal many times with different variables.

jenny
  • 933
  • 2
  • 11
  • 26