how to separate user tables and master tables in sqlserver

Question

0.00/5 (No votes)

See more:

Hello,

In the below query run to display dmlquery,datbasename,hostname...etc. So user table and master table queries are displayed but i want only user table queries like select * from sys.tables where type='U' and to find which table name in each query and also database.

SQL

SELECT distinct  dest.TEXT AS [Query],DB_NAME() as dbname,dess.host_name as 'Host Name',deqs.execution_count as count,deqs.creation_time as start_time,deqs.last_execution_time AS [last_execution_time]
		FROM sys.dm_exec_query_stats AS deqs
		CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest 
		cross apply sys.dm_exec_sessions dess
		cross apply sys.objects so
		where dest.text like 's%' and dess.session_id = @@spid and so.type='U'
		ORDER BY deqs.last_execution_time DESC

thanks in advance

Posted 5-Oct-11 2:16am

K N R

Updated 5-Oct-11 18:51pm

v3

Add a Solution

Comments

Mehdi Gholam 5-Oct-11 8:55am

Your question makes no sense, please edit it and supply more information.

K N R 6-Oct-11 0:52am

Hi Mehdi,
thanks for your suggestion. i am providing some information regarding that query.
can you please help me...!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Answer 1 · 2011-10-05T06:15:00

Perhaps what you mean is that you want to retabulate what "SELECT * FROM sys.databases" returns without including the top four(4) tables of the results.

There are plenty of ways to do that but the easiest is to exclude the "owner_sid" field by tacking on a "WHERE [owner_id] != 0x01" to the above and that should give you the tablish output.

Something that always challenged me when I first signed on to T-SQL was the lack of an index in all this tabulation. Until I discovered "... IDENTITY(1,1) ..." in the help tome. 'Does it automatically during a CREATE TABLE pattern excersize and .. well look it up in help and see the light.

Happy index-adding!