SELECT * FROM OPENROWSET('SQLNCLI', 'SERVER=.;TRUSTED_CONNECTION=YES','select 1 EX_REQ_ID, * from sys.dm_exec_requests')

SELECT * FROM OPENROWSET('SQLOLEDB', 'DATA SOURCE=.;Trusted_Connection=yes; Integrated Security=SSPI;', 'select 1 EX_REQ_ID, * from sys.dm_exec_requests')

SELECT * FROM OPENROWSET('SQLOLEDB', '.';'sa';'/''],lp123','select 1 EX_REQ_ID, * from sys.dm_exec_requests')

SELECT * FROM OPENROWSET('SQLNCLI','.';'sa';'/''],lp123','SET NOCOUNT ON; EXEC [TUAN_BIZ_SYSTEM].DBO.COLS22;')

there should be "set nocount on" if not only one 'select' in SP, ps. only the first result set would be return

& in SQL Server 2012, dynamic SQL needs "with result set ((ID INT))" (something like that) in EXEC statement of SP

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=.;Uid=sa;Pwd=/''],lp123;Database=msdb', 'SELECT db_name() name')

SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=.;Uid=sa;Pwd=/''],lp123;Database=msdb', 'SELECT db_name() name')

SELECT * FROM OPENROWSET('SQLNCLI',

'Server=.;Trusted_Connection=yes;',

'exec sp_msforeachdb ''if ''''?'''' = ''''msdb'''' begin use [?]; select db_name() name;  end'' with result sets ((name nvarchar(max)))')

We can use "database" attribute instead of using "initial catalog". (sp_msforeachdb is another...stupid way. XD)

metadata:

declare @a nvarchar(max) = 'select @@servername'

select * from sys.dm_exec_describe_first_result_set(@a, null, 0)

select * into ttc 

from openrowset('sqlncli','SERVER=.\node1;TRUSTED_CONNECTION=YES',

'SET NOCOUNT ON; SET FMTONLY OFF; 

exec sp_describe_first_result_set @tsql = N''SP_WHO''')

Backup & Restore

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)

AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

truncate all table

sp_msforeachtable 'print (''truncate table ?'' + char(10) + ''go'' + char(10) + ''print ''''? done'''''' + char(10) + ''go'' + char(10))'

Disk Space

select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a - See more at: http://learnmysql.blogspot.tw/2012/06/simple-script-to-find-free-space-in-sql.html#sthash.cVLxMxKY.dpuf

(hey... Navjyot, I always think your scripting skill sucks, however, this select is useful...XD)

Hekaton

http://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx