if OBJECT_ID('UTIL.index_definition','fn')is not null

drop function UTIL.index_definition

go

create function UTIL.index_definition

(

@schema sysname,

@table sysname,

@index sysname,

@include_on_storage bit = 1

)

returns varchar(max)

/*---------------------------------------------------------------------------------

select UTIL.index_definition

('dw', 'FactCompareResult','FCRBCKIdx',1 )

select UTIL.index_definition

('dw', 'FactCompareResult','FCRBCKIdx',0 )

Dynamic invocation:

set nocount on

print 'set nocount on'

select 'select UTIL.index_definition(''' +

s.name...

• This reply was modified 9 months, 2 weeks ago by SQL Guy 1.

Thanks a lot, Drew.

It works as expected.

It's just a select from 3 tables, and 2 of them are with left join. For aggregations, I would have done it on SQL Server side. Now I am thinking...

Thanks a lot Phil. It worked as expected.

I also have another problem. I need to filter some table with 2 conditions with AND, like in SQL Server it would be

WHERE...

I ran $psversiontable in all 3 environments - ISE, VS Code, Command-line utility, and the version is all the same - 5.1.19041.2673

Thanks you Thom, I found it in Tools/Options. It works great now!

Thanks again!

Yes Thom, I am doing exactly that. Now I tried results to TEXT and it works fine. But the TEXT results when I execute the query, is running very slowly....

Thanks a lot Johan. This is exactly what I was looking for.

Thanks all for your replies.

Some of you are asking for full DDL and/or execution plan. Unfortunately, I cannot do that. At our place we have very strict rules about security...

Thanks both Frederico and Johan. That works exactly as I wanted. Wanted to mark Johan's response as an answer too, but the site apparently does not allow it for two...

Tried TRUNCATE - no errors. Tried DELETE - 0 rows affected.

But I probably found the reason, this is likely to be a cloned database, that was created with DBCC CLONEDATABASE....

Ran it now. Did not help. Also tried DBCC checkdb. Did not help either. In an output message it displayed There are 0 rows in 0 pages for object <table_name>...

All tables are in dbo schema. None of tables that I've checked so far with select count(*) have records.

Thanks both Brian and Scott, but unfortunately nothing worked in my case.

@Brian: This query returns empty result set:

SELECT spid
FROM master..sysprocesses 
WHERE spid > 50 AND dbid =...

No. And frankly, I never worked with it. Can you please give more details of how I can use it?

Thanks