How to find a string value in all the string columns of a table/view in SQL Server ?

 

Introduction

I am sure many times we all might have come across situations where we need to search/find a string value in all the string columns of a given table/view in SQL Server and return the matching rows from that table.

Unfortunately, we do not have any straight forward way to do this till date AFAIK. Hence, the below script can prove to be quite handy in this situation -

USE DBName --Replace with the DB in which the table resides

GO

--Declare variables and initialize them

DECLARE @TableSchema AS VARCHAR(50) = 'SchemaName' --Replace this with the name of Schema of the Table/View

DECLARE @TableName AS VARCHAR(50) = 'TableName' --Replace this with the name of the Table/View to search

DECLARE @SearchString AS VARCHAR(50) = 'SearchString' --Replace this with actual SearchString

DECLARE @Qry AS NVARCHAR(MAX)

DECLARE @Columns AS VARCHAR(MAX)

--Prepare the columns

SET @Columns = STUFF((

SELECT 

  '+' + CASE WHEN IS_NULLABLE = 'YES' THEN 'ISNULL(' + C.COLUMN_NAME + ','''')' ELSE C.COLUMN_NAME END

FROM 

  INFORMATION_SCHEMA.COLUMNS C

WHERE

  C.TABLE_SCHEMA = COALESCE(@TableSchema,C.TABLE_SCHEMA)

  AND C.TABLE_NAME = COALESCE(@TableName,C.TABLE_NAME)

  AND C.DATA_TYPE IN ('CHAR','NCHAR','NTEXT','NVARCHAR','TEXT','VARCHAR')

FOR XML PATH('')),1,1,'')

--Prepare the Query

SET @Qry = N' SELECT ' +

            '  * ' +

            ' FROM ' +

            @TableSchema + '.' + @TableName +

            ' WHERE  ' +

              @Columns + ' LIKE ''%' + @SearchString + '%'''  

--Execute the Query

EXEC SP_EXECUTESQL @Qry

Please note that the above script works only for the following column types - CHAR,NCHAR,NTEXT,NVARCHAR,TEXT,VARCHAR

Njoy searching….

T-SQL to find Fragmented Indexes

Fragmentation of Indexes is one of the reason for low performing queries resulting in a poor application performance.
Today, I will present a simple script which will help in identifying the level of fragmentation in a Database.

--Replace this with the name of the Database for which we want to find the fragmentation.

USE <DBName> 

GO

DECLARE @DBName AS VARCHAR(10) = 'DBName'

DECLARE @DBID AS INT = DB_ID(@DBName)

DECLARE @AllowedFragmentation AS INT = 70 --A acceptable value in Percent(%) for fragmentation.

DECLARE @Qry AS VARCHAR(MAX)

SELECT

--@DBID [DBID],

--@DBName DBName,

PS.OBJECT_ID ObjectID,

COALESCE(T.name,V.name) ObjectName,

PS.index_id,

I.name IndexName,

PS.page_count AS TotalPages,

(PS.page_count * 8)/1024.0 as TotalMB,

((PS.page_count * 8)/1024.0) * (PS.avg_fragmentation_in_percent/100) as ReclaimableMB,

PS.avg_fragmentation_in_percent AvgFragmentationPercent

FROM

sys.dm_db_index_physical_stats (@DBID, NULL, NULL, NULL, NULL) AS PS

INNER JOIN sys.indexes AS I

ON PS.OBJECT_ID = I.OBJECT_ID

AND PS.index_id = I.index_id  

LEFT JOIN sys.tables T

ON T.object_id = I.object_id

LEFT JOIN sys.views V

ON V.object_id = I.object_id

WHERE

PS.database_id = @DBID

AND PS.avg_fragmentation_in_percent > @AllowedFragmentation 

ORDER BY    

PS.avg_fragmentation_in_percent DESC 

Here, I have considered 70% fragmentation as an acceptable level of fragmentation.

Hope, this helps.

Lookup functions in SSRS 2008 R2 - III

Introduction

This is the final post to discuss the last Lookup function Multilookup(). The other two – Lookup() and LookupSet() have already been discussed in my previous posts.

Syntax

Multilookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound. The only difference from the previous lookup functions is that, here this is a VariantArray.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, the value from the first matching row will be returned for all the values in the source expression. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the previous posts of Employees & Departments, suppose we want to display the name of the departments to which the Employee is related to, we can use the following expression -

Table Structure of Employees Table

ID	EmployeeID	DepartmentIDs
1	1	1,2,3
2	2	2,3

Table Structure of Departments Table

ID	Name
1	D1
2	D2
3	D3

Required Output

ID	EmployeeID	Departments
1	1	D1,D2,D3
2	2	D2,D3

To get the Departments, we can use the following expression in SSRS -

=Join(MultiLookup(Split(Fields!DepartmentIDs.Value,","),

   Fields!ID.Value,Fields!Name.Value,"Departments")),

   ",")

Here, we have used 3 functions -

1. Split() – To convert the comma separated DepartmentIDs into a value array.


2. Multilookup() – To find the Name of departments for the matching ID.


3. Join() – Prepare the comma separated string for the names returned by the Multilookup() as array.

Conclusion

So, with this we have covered all the 3 lookup functions provided in SSRS 2008 R2 and have learned how to make best use of them to fetch data from multiple datasets.

Lookup functions in SSRS 2008 R2 - II

 

Introduction

In my last post on Lookup functions related to SSRS 2008 R2, I had explained Lookup(). It is used to fetch the first matching value from the other DataSet. Now, what if we want all the matching values from the other DataSet. Here, the LookupSet() function comes handy. Let’s try to understand this.

Syntax

LookupSet(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter. 

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression for each row. 

Note, If multiple matches are found, all the values from the matching rows will be returned. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

Returns – A VariantArray, or Nothing if there is no match.

Example

Continuing with the same example from the last post of Employees & Departments, suppose we want to display the name of all the departments to which the Employee is related to, we can use the following expression -

=JOIN(LookupSet(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments"),",")

Here, we have used 2 functions of SSRS,

1. LookupSet() – To get an Variant array of the matching values. Here, the DepartmentID field will be matched with the ID field of the Departments Dataset and the Name from all the matching rows will be returned.


2. JOIN() – To join all the elements of the array as a comma separated string.

Hope, I have explained clearly the use of LookupSet() funciton. Next, I will blog about the last Lookup function – MultiLookup().

Lookup functions in SSRS 2008 R2 - I

 

Introduction

Most of us who are regularly working with SSRS have always felt the need of some way through which multiple DataSets could be joined – something similar to JOINs of SQL. But there was no straight forward way to get it done in SSRS until the release of SSRS 2008 R2. SSRS 2008 R2 came up with Lookup Functions which plays a very handy role in getting the things done in a very simple way. However, these functions can not be taken as a alternate to JOINs. As their name suggests, they are just lookups helping us to retrieve values from a dataset that has name/value pairs and that is not bound to the data region.

Different Flavors

We are provided with 3 different lookup functions -

1. Lookup(source_expression, destination_expression, result_expression, dataset)
2. LookupSet(source_expression, destination_expression, result_expression, dataset)
3. Multilookup(source_expression, destination_expression, result_expression, dataset)

Let’s take up them one by one

1. Lookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be evaluated in the current scope – generally the DataSet with which the Tablix is bound.

destination_expression – The field in which the source expression will be looked in. This field will belong to the dataset provided in the same function as the last parameter.

result_expression – The field we want to retrieve from the destination DataSet for the matching source_expression & destination_expression. 

Note, If multiple matches are found, the value from the first matching row will be returned. And we can not use any aggregate functions in the result_expression.

Dataset – The Dataset in which we want to look the values into.

E.g. Suppose, we are having 2 Datasets – EmployeeDetails & Departments in SSRS. The tablix is bound to EmployeeDetails and in one of the columns we need to display the DepartmentName to which the Employee in each row belongs to. The below expression will help us to fetch the Name of the department from the Departments Dataset.

=Lookup(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments")

Here, the DepartmentID field from the Employees Dataset will be matched with the ID field of the Departments Dataset and the Name for the matching row will be returned.

So, we have seen how we can make use of Lookup() and get the matching value. Will continue with the other two functions in my next blog.

Awarded as Microsoft Community Contributor 2011

I am really honored to receive this Award from Microsoft and would take this opportunity to thank my colleague Mr. Ankit Shah & my close friend Mr. Hemant Goswami who always kept motivating me for contributing to the Community. 

I am very excited on the receipt of this award and I promise to continue serving the community.

Rebuild all the Indexes of a SQL Database in one go

Introduction

In my last post, I had explained what could be the best value of Fill Factor for the indexes in SQL Server and had promised to show a handy way to ReBuild all the indexes including the ones created on the Indexed Views.

Implementation

USE DBName

GO

DECLARE @tsql NVARCHAR(MAX)  

DECLARE @fillfactor INT

SET @fillfactor = 70 

SELECT @tsql = 

  STUFF(( SELECT DISTINCT 

           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

          FROM 

           sysobjects o 

          INNER JOIN sysindexes i 

           ON o.id = i.id 

          WHERE 

           o.xtype IN ('U','V')

           AND i.name IS NOT NULL

          FOR XML PATH('')), 1,1,'')

--PRINT @tsql          

EXEC sp_executesql @tsql  

Conclusion

This way, we can easily rebuild all the existing indexes on the Tables as well as the Indexed Views of the selected Database with an option to set the fillfactor as well. Hope, this script will prove to be handy.