有没有可能执行SQL查询但不显示结果?
类似于
Select * from Table_Name
运行此查询后,结果不应在 SQL Server 中显示。
有没有可能执行SQL查询但不显示结果?
类似于
Select * from Table_Name
运行此查询后,结果不应在 SQL Server 中显示。
我很惊讶社区无法轻松找到这个的用例。大型结果集会占用客户端内存,如果有许多SSMS窗口处于活动状态可能会成为问题(我通常会打开2-3个SSMS实例,每个实例都有50-70个活动窗口)。在某些情况下,例如Cyril的示例中,SSMS可能会耗尽内存,无法处理大型结果集。例如,我曾经需要调试返回数亿行的存储过程。在我的开发机上,如果不丢弃结果,则无法在SSMS中运行。该过程是用作数据源加载数据仓库表的SSIS包的一部分。在SSMS中进行调试涉及进行非功能性更改(因此结果集对我没有兴趣),并检查执行统计信息和实际查询执行计划。
-- Written by David Zanke
-- Return all records modified by a specified user on or after a specified date.
If mod date does not exist, return row anyhow
Set Nocount on
Declare @UserName varchar(128) = 'zanked'
, @UpdatedAfterDate Varchar( 30) = '2016-10-08'
, @TableName varchar( 128)
, @ModUser varchar( 128)
, @ModTime varchar( 128)
, @sql varchar( 2000 )
-- In a perfect world, left join would be unecessary since every row that captures the last mod user would have last mod date.
-- Unfortunately, I do not work in a perfect world and rows w/ last mod user exist w/o last mod date
Declare UserRows Cursor for Select distinct c1.table_name, c1.column_name, c2.column_name From INFORMATION_SCHEMA.COLUMNS c1
Left Join INFORMATION_SCHEMA.COLUMNS c2 On c1.Table_Name = c2.Table_Name And c2.Column_name like '%DTTM_RCD_LAST_UPD%'
Where c1.column_name like '%UPDATED_BY_USER%'
Open UserRows
Fetch UserRows Into @tablename, @ModUser, @ModTime
While ( @@FETCH_STATUS = 0 )
Begin
-- capture output from query into a temp table
Select @sql = 'Select ''' + @TableName + ''' TableName, * Into ##HoldResults From ' + @TableName + ' Where ' + @ModUser + ' = ''' + @userName + ''''
+ Case When @ModTime Is Null Then '' Else ' And ' + @ModTime + ' >= ''' + @UpdatedAfterDate + '''' End
Exec ( @sql)
-- only output where rows exist
If @@ROWCOUNT > 0
Begin
Select * from ##HoldResults
End
Drop Table ##HoldResults
Fetch UserRows Into @tablename, @ModUser, @ModTime
End
Close UserRows;
Deallocate UserRows