如何在sqlserver中分离用户表和主表 [英] how to separate user tables and master tables in sqlserver

问题描述

你好，

在下面的查询中运行以显示dmlquery，datbasename，hostname ...等.因此显示了用户表和主表查询，但我只希望用户表查询(如select * from sys.tables中的type ="U'')并在每个查询以及数据库中查找哪个表名.

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.

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

推荐答案

也许您的意思是您希望重新列出"SELECT * FROM sys.databases"返回的内容，而不包括以下内容的前四(4)个表结果.

有很多方法可以做到这一点，但最简单的方法是在上面加上"WHERE [owner_id]！= 0x01"，从而排除"owner_sid"字段，这应该为您提供平淡的输出.

当我第一次登录T-SQL时，一直困扰我的是所有这些列表中都缺少索引.直到我在帮助书中发现"... IDENTITY(1,1)...". "在执行CREATE TABLE模式期间自动执行此操作，并在帮助中查找并查看指示灯.

祝索引愉快！

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!

这篇关于如何在sqlserver中分离用户表和主表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！