T-SQL : Query to fetch report subscription details from SSRS

In SSRS, to find who has created/modified the report subscription without using the management site, the following query can be used,

[sourcecode language=”sql” wraplines=”false”]
select
reps.Name as ReportName
,usr.UserName createdby
from Subscriptions subs
join Catalog reps on subs.Report_OID = reps.ItemID
join Users usr on subs.OwnerID = usr.UserID​
[/sourcecode]

This is will help in finding who has created the subscriptions in report server DB, rather trying by each users in report manager. When it would be useful ? take for instance, if your customer wants some of the email IDs to be removed in the existing subscriptions. Unless we have the information that who has created the existing subscriptions, the email ids cannot be edited.

Happy Querying 😉

Posted in T-SQL | Leave a comment

T-SQL : Get all folder names from SSRS Reports Manager/Server

We all know, we can query the report server database and get very useful information, such as reports details, directory and schedules etc. This particular thread belongs to one such category, this query will provide you all available folders from a report server

[sourcecode language=”sql” wraplines=”false”]

select
ltrim(rtrim(Name))
from [dbo].[Catalog]
where type =1
and ParentID is not null

[/sourcecode]

I will soon post other queries that will provide much more useful properties/information from report server.

Happy Querying 😉

Posted in T-SQL | Tagged , , , , , , , | Leave a comment

T-SQL : Get count of all tables in a database

The following query will list the count of records in all tables of a database. These queries are based on CURSORS,

Query 1:
Query to get individual results,
[sourcecode language=”sql” wraplines=”false”]
DECLARE @SQL VARCHAR( max ),
@TableName VARCHAR (255 ),
@SchemaName VARCHAR (50 ) = ‘dbo’
DECLARE TableCount CURSOR FOR
SELECT table_name
FROM INFORMATION_SCHEMA .tables
WHERE table_schema = @SchemaName
OPEN TableCount
FETCH next FROM TableCount INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘select ”’ + @TableName
+ ”’ as TableName,count(*) [Count] from ‘
+ @SchemaName + ‘.’ + @TableName
PRINT @SQL
FETCH next FROM TableCount INTO @TableName
EXEC (@SQL )
END
CLOSE TableCount
DEALLOCATE TableCount

[/sourcecode]

Query 2:
Results stored in singe temp table,
[sourcecode language=”sql” wraplines=”false”]
DECLARE @SQL VARCHAR( max ),
@TableName VARCHAR (255 ),
@SchemaName VARCHAR (50 ) = ‘dbo’

CREATE TABLE #TableCount
(
TableName VARCHAR (255 ),
TableCount INT
)

DECLARE TableCount CURSOR FOR
SELECT table_name
FROM INFORMATION_SCHEMA .tables
WHERE table_schema = @SchemaName

OPEN TableCount
FETCH next FROM TableCount INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = ‘insert into #TableCount
select ”’ + @TableName
+ ”’ as TableName,count(*) [Count] from ‘
+ @SchemaName + ‘.’ + @TableName
PRINT @SQL
FETCH next FROM TableCount INTO @TableName
EXEC (@SQL )
END
CLOSE TableCount
DEALLOCATE TableCount
SELECT *
FROM #tableCount
DROP TABLE #tableCount

[/sourcecode]

Happy Querying 😉

Posted in T-SQL | Tagged , | Leave a comment

T-SQL : List of scheduled reports in SSRS

The following query will list all the available report subscriptions in SSRS. The reportserver database basically holds all metadata information [reports, users, schedules, roles etc] of all reports and thereby, querying the same will provide us a lot of useful results, one such example is shown below

[sourcecode language=”sql” wraplines=”false”]
SELECT cat.Name AS ReportName,
cat.Path AS ReportPath,
sub.Description AS Destination,
usr.UserName AS ModifiedBy,
sub.LastStatus LatestStatus
FROM ReportServer..Catalog cat
JOIN ReportServer..reportSchedule repsch
ON cat.ItemID = repsch.ReportID
JOIN ReportServer..Subscriptions sub
ON repsch.SubscriptionID = sub.SubscriptionID
JOIN ReportServer..Users usr
ON usr.UserID = cat.ModifiedByID
WHERE cat.Type = 2
ORDER BY reportname
[/sourcecode]

Happy Querying 😉

Posted in T-SQL | Tagged , , , , , , | Leave a comment

T-SQL : How to get columns names of all tables

How to get columns names from all tables of a database ? The query should return all the columns of all tables in a particular database, in a order like how it is stored in the tables

The following queries will perform the same,

Query 1:

[sourcecode language=”sql” wraplines=”false”]

SELECT b.name AS TableName,
a.name AS ColumnName
FROM sys.columns a
JOIN sys.tables b
ON a.object_id = b.object_id
ORDER BY tablename,
column_id

[/sourcecode]

Query 2:

[sourcecode language=”sql” wraplines=”false”]

SELECT a.table_name,
column_name
FROM information_schema.columns a
JOIN information_schema.tables b
ON a.table_name = b.table_name
WHERE b.table_type = ‘BASE TABLE’
ORDER BY a.table_name,
ordinal_position

[/sourcecode]

Query 3:

[sourcecode language=”sql” wraplines=”false”]

SELECT b.name,
a.name
FROM sys.all_columns a
JOIN sys.objects b
ON a.object_id = b.object_id
WHERE b.type = ‘U’
ORDER BY b.name,
a.column_id

[/sourcecode]

Output:
ColumnNames

Happy Querying 😉

Posted in T-SQL | Tagged , , , , , | Leave a comment

T-SQL : Gaps and Islands Problem

The table below has set of names and their date values, where some of the date values are missing by month for each Name
For example, the name ‘Test1′ have date range like ’01-Apr-2013′,’01-Jun-2013’and ’01-Aug-2013’ and it is clear that a datevalue for the month of may and July are missing.

The query has to find the missing date values and insert them back to the same table for each names available in it

A Sample of Missing record is shown below,

1, ‘Test1′,’01-May-2013’
1, ‘Test1′,’01-July-2013’

[sourcecode language=”sql” wraplines=”false”]
DECLARE @table TABLE
(
ID INT,
Name VARCHAR(10 ),
DateValue DATETIME
)

Insert into @table
Select 1, ‘Test1′, ’01-Apr-2013’
Union All
Select 1, ‘Test1′, ’01-Jun-2013’
Union All
Select 1, ‘Test1′, ’01-Aug-2013’
Union All
Select 2, ‘Test2′, ’01-Jun-2013’
Union All
Select 2, ‘Test2′, ’01-Aug-2013’
Union All
Select 2, ‘Test2′, ’01-Oct-2013’
Union All
Select 3, ‘Test3′, ’01-Sep-2013’
Union All
Select 3, ‘Test3′, ’01-Nov-2013’

select * from @table — Before Inserting

;WITH cte AS
(
SELECT ID, Name,DateValue FROM
(SELECT ID ,Name, Min( DateValue ) DateValue FROM @table GROUP BY ID,Name) x
UNION ALL
SELECT ID, Name,Dateadd (mm , 1, DateValue ) FROM cte b
WHERE EXISTS
(SELECT 1 FROM @table a
WHERE a. ID = b .id
AND b. DateValue<a .DateValue)

)

INSERT INTO @table
SELECT b. ID,
b .Name,
b . DateValue
FROM @table a
RIGHT JOIN cte b
ON a. ID = b .ID
AND a. DateValue = b .DateValue
WHERE a. ID IS NULL

SELECT *
FROM @table
ORDER BY ID,
DateValue
[/sourcecode]

Posted in T-SQL | Tagged , , , , , , | Leave a comment

T-SQL : An Interesting Question – 3

The requirement is as follows,

Filter out records that has three or more words in it

(or)

Filter out records that has two or more spaces in it

There are several ways to perform this and i would like to show some of them,

[sourcecode language=”sql” wraplines=”false”]
Declare @table Table(words varchar(50))
insert into @table
select ‘Get the query’ union
select ‘Two words’ union
select ‘Three’ union
select ‘Explain’ union
select ‘See the output’ union
select ‘Execute the Query’ union
select ‘Find the Names’ union
select ‘single’ union
select ‘see the output below’

select words from @table
where words like ‘% % %’

select words from @table
where words LIKE ‘%| %| %’ ESCAPE ‘|’

select words from @table
where words LIKE ‘_% _% _%’

select words from @table
where DATALENGTH(words) – DATALENGTH(REPLACE(words,’ ‘,”))>=2

select words from @table
where CHARINDEX(‘ ‘,words, CHARINDEX(‘ ‘,words, 1) + 1) > 0

select words from @table
where PATINDEX(‘% % %’,words)> 0
[/sourcecode]

Happy Querying 😉

Posted in T-SQL | Tagged , , | Leave a comment

T-SQL : An Interesting Question – 2

Another interesting question that I have read recently, which involves some string operations.

Question:
The whole string given is a alphanumeric (for ex: AP1AA). The requirement is to Stuff ‘0’ before the single digit number (0-9) which is enclosed by Alphabets (AP1AA)).

For Ex:

If you are given a string like AP2JJ then it should be stuffed with a zero before the single digit numerical (2 in this case) and the output should be AP02JJ. When the numeric is of 2 digits, no actions are needed.

Query:

[sourcecode language=”sql” wraplines=”false”]
DECLARE @table TABLE (ExString varchar (12))
INSERT INTO @table
SELECT ‘AP10AA’ UNION
SELECT ‘AA10B’ UNION
SELECT ‘AAA123’ UNION
SELECT ‘AA1AB1’ UNION
SELECT ‘AA2B1’ UNION
SELECT ‘AA03A’ UNION
SELECT ‘AA1234’ UNION
SELECT ‘2BB’ UNION
SELECT ‘A9B’ UNION
SELECT ‘A0B’ UNION
SELECT ‘A10B’

SELECT
ExString AS ExString_Before ,
CASE WHEN ExString LIKE ‘%[a-z][0-9][a-z]%’
THEN STUFF (ExString, patindex(‘%[0-9]%’ ,ExString), 0,’0′ )
ELSE ExString
END ExString_After
FROM @table
[/sourcecode]

I have shared my query to perform the above stuff operations. Any suggestions and new ideas are welcomed

Happy Querying 😉

Posted in T-SQL | Tagged , , , , | Leave a comment

T-SQL : An Interesting Question – 1

Recently I found this interesting question asked in a SQL forum,

Let’s say you have 5 columns in a table and you want to know whether these columns values satisfies the following conditions,

  • Three columns should have values > 19
  • Two columns should have values < 20
  • Two columns should have values in Odd
  • Three columns should have values in Even

I have tried and come up with the following query. Share your thoughts and queries through comments,

[sourcecode language=”sql” wraplines=”false”]

declare @table table (Col1 int,Col2 int,Col3 int,Col4 int,Col5 int)
insert into @table
select 4, 7, 25, 38, 40 union
select 2, 5, 21, 40, 42 union
select 2, 4, 18, 17, 15 union
select 1, 3, 19, 17, 15 union
select 2, 4, 21, 22, 23 union
select 1, 5, 45, 40, 48 union
select 1, 42, 40, 5, 56

;with cte as (
select Col1,Col2,Col3,Col4,Col5,
case when Col1%2=0 then 1 else 0 end EvenCheck1,
case when Col2%2=0 then 1 else 0 end EvenCheck2,
case when Col3%2=0 then 1 else 0 end EvenCheck3,
case when Col4%2=0 then 1 else 0 end EvenCheck4,
case when Col5%2=0 then 1 else 0 end EvenCheck5,
case when Col1%2=1 then 1 else 0 end OddCheck1,
case when Col2%2=1 then 1 else 0 end OddCheck2,
case when Col3%2=1 then 1 else 0 end OddCheck3,
case when Col4%2=1 then 1 else 0 end OddCheck4,
case when Col5%2=1 then 1 else 0 end OddCheck5,
case when Col1>19 then 1 else 0 end GtrCheck1,
case when Col2>19 then 1 else 0 end GtrCheck2,
case when Col3>19 then 1 else 0 end GtrCheck3,
case when Col4>19 then 1 else 0 end GtrCheck4,
case when Col5>19 then 1 else 0 end GtrCheck5,
case when Col1<20 then 1 else 0 end LsrCheck1,
case when Col2<20 then 1 else 0 end LsrCheck2,
case when Col3<20 then 1 else 0 end LsrCheck3,
case when Col4<20 then 1 else 0 end LsrCheck4,
case when Col5<20 then 1 else 0 end LsrCheck5
from @table
)

select Col1,Col2,Col3,Col4,Col5  from cte
where EvenCheck1+EvenCheck2+EvenCheck3+EvenCheck4+EvenCheck5 = 3
and OddCheck1+OddCheck2+OddCheck3+OddCheck4+OddCheck5 = 2
and GtrCheck1+GtrCheck2+GtrCheck3+GtrCheck4+GtrCheck5 = 3
and LsrCheck1+LsrCheck2+LsrCheck3+LsrCheck4+LsrCheck5 = 2

[/sourcecode]

The same case when can be also used directly in a where condition as shown below,

[sourcecode language=”sql” wraplines=”false”]
declare @table table (Col1 int,Col2 int,Col3 int,Col4 int,Col5 int)
insert into @table
select 4, 7, 25, 38, 40 union
select 2, 5, 21, 40, 42 union
select 2, 4, 18, 17, 15 union
select 1, 3, 19, 17, 15 union
select 2, 4, 21, 22, 23 union
select 1, 5, 45, 40, 48 union
select 1, 5, 40, 42, 56

SELECT     * FROM     @table
WHERE
(case when Col1%2=0 then 1 else 0 end+
case when Col2%2=0 then 1 else 0 end+
case when Col3%2=0 then 1 else 0 end+
case when Col4%2=0 then 1 else 0 end+
case when Col5%2=0 then 1 else 0 end) =3
and
(case when Col1%2=1 then 1 else 0 end +
case when Col2%2=1 then 1 else 0 end +
case when Col3%2=1 then 1 else 0 end +
case when Col4%2=1 then 1 else 0 end +
case when Col5%2=1 then 1 else 0 end ) = 2
and
(case when Col1>19 then 1 else 0 end +
case when Col2>19 then 1 else 0 end +
case when Col3>19 then 1 else 0 end +
case when Col4>19 then 1 else 0 end +
case when Col5>19 then 1 else 0 end ) = 3
and
(case when Col1<20 then 1 else 0 end +
case when Col2<20 then 1 else 0 end +
case when Col3<20 then 1 else 0 end +
case when Col4<20 then 1 else 0 end +
case when Col5<20 then 1 else 0 end ) = 2

[/sourcecode]

Output:

image

 

Happy Querying 😉

Posted in T-SQL | Tagged , , | Leave a comment

Split Comma Separated values

In this thread I would like to share my SQL scripts to split the comma separate values in to  individual values. This script can be effectively used when passing multivalue parameters from the SSRS report to stored procedures.

TSQL – CODE:

[sourcecode language=”sql” wraplines=”false”]
DECLARE @text VARCHAR (50)

DECLARE @Delimiter CHAR (1)

DECLARE @length INT

DECLARE @table TABLE

(

value VARCHAR(50 )

)

SET @text = ‘1,2,4,887,77889,1,132,4,5,455,4478’

SET @Delimiter = ‘,’

SET @length = Len( @text)

WHILE ( @length != 0 )

BEGIN

IF( Charindex(@delimiter , @text) <> 0 )

BEGIN

INSERT INTO @table(value )

SELECT LEFT( @text, Charindex(@Delimiter , @text) – 1 )

SET @text = RIGHT(@text , Len (@text) – Charindex(@Delimiter , @text))

SET @length= Len(@text )

END

ELSE

BEGIN

INSERT INTO @table(value )

SELECT @text

SET @length = 0

END

END

SELECT *

FROM   @table
[/sourcecode]

Result:

image_thumb

This can be also created as a function in SQL Server as shown below,

[sourcecode language=”sql” wraplines=”false”]
CREATE FUNCTION dbo.Split(@text      VARCHAR (MAX),

@Delimiter CHAR (1))

RETURNS @table TABLE (VALUE VARCHAR(50))

AS

Begin

DECLARE @length INT

SET @length = Len(@text)

WHILE ( @length != 0 )

BEGIN

IF( Charindex(@delimiter, @text) <> 0 )

BEGIN

INSERT INTO @table(value)

SELECT LEFT(@text, Charindex(@Delimiter, @text) – 1)

SET @text = RIGHT(@text, Len (@text) – Charindex(@Delimiter, @text))

SET @length= Len(@text)

END

ELSE

BEGIN

INSERT INTO @table(value)

SELECT @text

SET @length = 0

END

END

RETURN

END;
[/sourcecode]

To use the function, execute below code,

[sourcecode language=”sql” wraplines=”false”]
SELECT * FROM dbo.SPLIT ( ‘1,2,4,887,77889,1,132,4,5,455,4478’,’,’)
[/sourcecode]

OUTPUT:

c

Posted in T-SQL | Tagged , , , , , , , | 1 Comment