locked
T-SQL Guru needed! Apply within... RRS feed

  • General discussion

  • April fools out of the way, now let's find an April genius!

    The name "April" is derived from the Latin verb "aperire", meaning "to open" in reference to being the season when trees & flowers start to "open".

    And I have to say, judging from the quality of contributions in recent months, I can't wait to OPEN and read this month's community gold!

    Things are hotting up in TechNet, and the Wiki has become a shining example of what the community has to offer.

    If you can find the time to enrich us with your latest revelations, or some fascinating facts, then not only will you build up a profile and name for yourself within the gaze of Microsoft's very own glitterati, but you will be adding pages to the most respected source for Microsoft knowledge base articles. This could not only boost your career, but would benefit generations to come!

    So don't be an April fool. Please realise the potential of this platform, realise where we are going, and join us in growing this community, learning more about you, and opening the minds of others!

    All you have to do is add an article to TechNet Wiki from your own specialist field. Something that fits into one of the categories listed on the submissions page. Copy in your own blog posts, a forum solution, a white paper, or just something you had to solve for your own day's work today.

    Drop us some nifty knowledge, or superb snippets, and become MICROSOFT TECHNOLOGY GURU OF THE MONTH!

    This is an official Microsoft TechNet recognition, where people such as yourselves can truly get noticed!

    HOW TO WIN

    1) Please copy over your Microsoft technical solutions and revelations to TechNet Wiki.

    2) Add a link to it on THIS WIKI COMPETITION PAGE (so we know you've contributed)

    3) Every month, we will highlight your contributions, and select a "Guru of the Month" in each technology.

    If you win, we will sing your praises in blogs and forums, similar to the weekly contributor awards. Once "on our radar" and making your mark, you will probably be interviewed for your greatness, and maybe eventually even invited into other inner TechNet/MSDN circles!

    Winning this award in your favoured technology will help us learn the active members in each community.

    Feel free to ask any questions below.

    More about TechNet Guru Awards

    Thanks in advance!
    Pete Laker


    #PEJL

    Got any nice code? If you invest time in coding an elegant, novel or impressive answer on MSDN forums, why not copy it over to the one and only TechNet Wiki, for future generations to benefit from! You'll never get archived again!

    If you are a member of any user groups, please make sure you list them in the Microsoft User Groups Portal. Microsoft are trying to help promote your groups, and collating them here is the first step.

    Wednesday, April 2, 2014 9:17 PM

All replies

  • It is really very useful! I'm look forward more and more good articles.

    Serena

    Monday, April 7, 2014 11:12 AM
  • We have one article so far!

     

    And two weeks to go!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, April 16, 2014 7:57 AM
  • Naomi entered the fray!

     

    3 more days to go!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, April 28, 2014 5:37 AM
  • hi Naomi,

    There is no need of using cursor, for example in the second section "Searching String in all Columns in all Tables of a Database". You can use CROSS APPLY for getting the inforamtion from all tables. 

    In any case, I think that it is much better to use direct solution, which build the dynamic query from the start for searching all tables in that case (or several specific tables or even one table). for example:
    http://ariely.info/Blog/tabid/83/EntryId/143/Searching-All-Columns-in-Table-tables.aspx


    [Personal Site] [Blog] [Facebook]signature

    Monday, April 28, 2014 12:28 PM
  • Can you elaborate on your idea? I am using a stored procedure. How would you call stored procedure with CROSS APPLY ?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 28, 2014 3:40 PM
  • BTW, I don't see how can I comment on that blog (does a user has to register first?), but your blog post has several errors. In the execute sp_ExecuteSQL command you're missing the N'parameters part and also the resulting SQL is quite strange and looks wrong.

    I also got this error executing it as is:

    Msg 457, Level 16, State 1, Line 1
    Implicit conversion of varchar value to text cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 28, 2014 3:46 PM
  • Hi Ed,

    I have ideas for 2 more articles, but most likely I will write them in May or later. It was hard to find time yesterday as we had a party with our friends at night.

    Here is my Facebook photo from that party.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles




    • Edited by Naomi N Monday, April 28, 2014 4:12 PM
    Monday, April 28, 2014 4:00 PM
  • Hi Naomi,

    The hyperlink is no good for your party photo. Make it public. Thanks.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Monday, April 28, 2014 4:06 PM
  • Ok, just for you Kalman I made it public. You can click on that link in my post.

    Also, the party was in our friends house - it was his birthday party.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Monday, April 28, 2014 4:14 PM
    Monday, April 28, 2014 4:12 PM
  • Hi Naomi

    1. Regarding the CROSS APPLY the Idea is to use table function for example.

    2. As I mentioned i think there is no need in CROSS APPLY as well and it can be done using the sample I posted in the link above.

    * Sorry  you can't comment on the blog directly (Yes you should Register before, but it is not working). I closed the option of comments since there where some security problem regarding the DNN application (this is a very old version of DotNetNuke and as much as I know all bugs and problems where fixed in the newer versions). I will check the post. It is possible the copy did not past well, I did execute the code before post it. Thanks for the comment. I will go to check now :-)

    But in first glance, This is look like a culture (collation) problem and probably adding N before the string will do the work


    [Personal Site] [Blog] [Facebook]signature

    Monday, April 28, 2014 8:23 PM
  • Hi again :-) This was fast,

    I check the query as it is (just copy from the blog directly to the SSMS) on SQL Server 2012 and 2014 and it work well for me. I even checked my code on AdventureWorks2012 database and It work OK (looking for the word 'Glenside' bring result in 1-2 sec from several tables as expected).

    * As I guess the code is bad, missing the N for the NVARCHAR that is use. I recommend to add N before the string (I will add it in several min to the blog).

    * My developing server is all new and clean so there are almost no databases.


    [Personal Site] [Blog] [Facebook]signature

    Monday, April 28, 2014 8:38 PM
  • I tried to fix your code, but I am still getting that same error which I am not sure how to resolve. Also, it looks like you embed search string directly into the SQL - not a very good practice:

    DECLARE @MyQuery NVARCHAR(MAX) = ''
     
    -- What do we want to find?
    -- If you need to search Multiple Strings then you shold use CLR Split Function as mentioned below*!
    DECLARE @SearchString nvarchar(100) = N'test'
     
    -- Where do we want to look for?
    -- If you want to search all tables then dont insert any value (NULL),
    -- If you want to search a specific table then insert the table name
    -- If you need to search several specific tables then you should use tables comma delimited as mentioned below!
    DECLARE @TableToFilter NVARCHAR(100) --= 'test'
     
    SET @MyQuery = (
        SELECT
            'SELECT * FROM (' + CHAR(10) +
            'SELECT ' +
                '''[' + TABLE_CATALOG + ']'' as TABLE_CATALOG, ''[' + TABLE_SCHEMA + ']'' as TABLE_SCHEMA, ''[' + TABLE_NAME + ']'' as TABLE_NAME,' +
                '''[' + COLUMN_NAME + ']'' as COLUMN_NAME, CAST(' + quotename(COLUMN_NAME) + ' AS NVARCHAR(MAX)) as Value' +
            ' FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' +
            -- Here we filter the search String we want to find.
            -- If you need to search Multiple Strings then you shold use CLR Split Function or User type,
            -- and replace the use of "Like" filter with the use of "IN" filter
            ' WHERE CAST([' + TABLE_NAME + '].[' + COLUMN_NAME + '] AS NVARCHAR(MAX)) LIKE ''%' + @SearchString + '%'' COLLATE DATABASE_DEFAULT ' +
            ') as [' + TABLE_CATALOG + TABLE_SCHEMA + TABLE_NAME + ']' +  CHAR(10) +
            CHAR(10) + 'UNION ALL' + CHAR(10)
        FROM (
            SELECT
                TABLE_SCHEMA,TABLE_CATALOG,TABLE_NAME,COLUMN_NAME
                -- Here we filter the tables that we want to search in.
                ,TableToFilter =  CASE
                    WHEN @TableToFilter IS NULL THEN 1
                    -- If you need to search in several specific tables, then you shold use CLR Split Function or User type,
                    -- and replace the use of "=" filter with the use of "IN" filter
                    WHEN NOT @TableToFilter IS NULL and TABLE_NAME = @TableToFilter THEN 1
                    ELSE 0
                END
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
        ) T1
        where TableToFilter = 1
        FOR XML PATH('')
    )
    SET @MyQuery = REPLACE(@MyQuery + 'END','UNION ALL'+ CHAR(10) +'END','')
    PRINT @MyQuery
    EXECUTE sp_executesql @MyQuery


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 28, 2014 8:40 PM
  • I tried to fix your code, but I am still getting that same error which I am not sure how to resolve. Also, it looks like you embed search string directly into the SQL - not a very good practice

    True :-)
    This was just a fast coding to give another option regarding your WIKI. I did not want to say that I think it can be done better on the WIKI page :-) Letting you to choose if and how to use it.

    * I will check the code you posted here.


    [Personal Site] [Blog] [Facebook]signature

    Monday, April 28, 2014 8:48 PM
  • I am only getting this error in one database but not in another. Also, your code selects views along with the tables.

    I ran my stored procedures against that same database that gives me the error message and my code worked. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 28, 2014 8:48 PM
  • I will check the code again, but since I get No error it will be harder for me to find the reason for error :-)

    In any case the idea is that this is the logic and there is no need in using cursor at all.

    * I checked the code u post here and it has some nice use of quotename function for example. There is no need of using cast to Unicode as I see it since "Returns a Unicode string". At this time I will leave the code as it is (I more interesting in the error on that specific database). I can check your code as well from the WIKI and use a combination of the codes maybe, if you said that your code work on that database as well.


    [Personal Site] [Blog] [Facebook]signature

    Monday, April 28, 2014 9:04 PM
  • Try creating a test table with different collations. May be you'll be able to get the error.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 28, 2014 9:09 PM
  • Also, the cursor shown in that blog utilizes a stored procedure to search for a particular string in a table. I guess you're suggesting to have one multi-purpose stored procedure that can search in one table vs. all tables and in one column vs. multiple columns. 

    This is also possible.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 28, 2014 9:12 PM
  • I checked on AdventureWorks2012 which use SQL_Latin1_General_CP1_CI_AS, but maybe there is a specific collation that bring the problem. (1) What is the collations of the database with the error? (2) can you reproduce the problem in a new database? If so, can you post me a database backup file with the problem?

    [Personal Site] [Blog] [Facebook]signature

    Monday, April 28, 2014 9:19 PM
  • Also, the cursor shown in that blog utilizes a stored procedure to search for a particular string in a table. I guess you're suggesting to have one multi-purpose stored procedure that can search in one table vs. all tables and in one column vs. multiple columns. 

    This is also possible.

    Yes I do, this is exactly my point.

    [Personal Site] [Blog] [Facebook]signature

    Monday, April 28, 2014 9:21 PM
  • I fixed your script in the meantime to not produce the error:

    DECLARE @MyQuery NVARCHAR(MAX) = ''
     
    -- What do we want to find?
    -- If you need to search Multiple Strings then you shold use CLR Split Function as mentioned below*!
    DECLARE @SearchString nvarchar(100) = N'test'
     
    -- Where do we want to look for?
    -- If you want to search all tables then dont insert any value (NULL),
    -- If you want to search a specific table then insert the table name
    -- If you need to search several specific tables then you should use tables comma delimited as mentioned below!
    DECLARE @TableToFilter NVARCHAR(100) --= 'test'
     
    SET @MyQuery = (
        SELECT
            'SELECT * FROM (' + CHAR(10) +
            'SELECT ' +
                '''[' + TABLE_CATALOG + ']'' as TABLE_CATALOG, ''[' + TABLE_SCHEMA + ']'' as TABLE_SCHEMA, ''[' + TABLE_NAME + ']'' as TABLE_NAME,' +
                '''[' + COLUMN_NAME + ']'' as COLUMN_NAME, CAST(' + quotename(COLUMN_NAME) + ' AS NVARCHAR(MAX)) COLLATE DATABASE_DEFAULT as Value' +
            ' FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' +
            -- Here we filter the search String we want to find.
            -- If you need to search Multiple Strings then you shold use CLR Split Function or User type,
            -- and replace the use of "Like" filter with the use of "IN" filter
            ' WHERE CAST([' + TABLE_NAME + '].[' + COLUMN_NAME + '] AS NVARCHAR(MAX)) LIKE ''%'' + @SearchString + ''%'' COLLATE DATABASE_DEFAULT ' +
            ') as [' + TABLE_CATALOG + TABLE_SCHEMA + TABLE_NAME + ']' +  CHAR(10) +
            CHAR(10) + 'UNION ALL' + CHAR(10)
        FROM (
            SELECT
                TABLE_SCHEMA,TABLE_CATALOG,TABLE_NAME,COLUMN_NAME
                -- Here we filter the tables that we want to search in.
                ,TableToFilter =  CASE
                    WHEN @TableToFilter IS NULL THEN 1
                    -- If you need to search in several specific tables, then you shold use CLR Split Function or User type,
                    -- and replace the use of "=" filter with the use of "IN" filter
                    WHEN NOT @TableToFilter IS NULL and TABLE_NAME = @TableToFilter THEN 1
                    ELSE 0
                END
            FROM INFORMATION_SCHEMA.COLUMNS C
            WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
    		
        ) T1
        where TableToFilter = 1
        FOR XML PATH('')
    )
    SET @MyQuery = REPLACE(@MyQuery + N'END','UNION ALL'+ CHAR(10) +'END','')
    PRINT @MyQuery
    
    
    EXECUTE sp_executesql @MyQuery, N'@SearchString NVARCHAR(100)', @SearchString = N'test'

    Look at the original error message I posted, I guess these were the offending collations. Also, I am wondering why did I see views in the result? Is there a way to filter them?

    The offending database collation is SQL_Latin1_General_CP1_CI_AS 

    I think the error comes from the fact that some tables may use this collation and some Latin1_General_CI_AS (may be it was explicitly specified for the columns).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, April 28, 2014 9:31 PM
    Monday, April 28, 2014 9:27 PM

  • Look at the original error message I posted, I guess these were the offending collations. Also, I am wondering why did I see views in the result? Is there a way to filter them?

    Yes Naomi, this is simple :-) we need to use left join to a table or view that have this information. for example adding:

    left JOIN INFORMATION_SCHEMA.TABLES T on C.TABLE_CATALOG = T.TABLE_CATALOG and T.TABLE_SCHEMA = T.TABLE_SCHEMA and C.TABLE_NAME = T.TABLE_NAME
    WHERE TABLE_TYPE = 'BASE TABLE'

    This will do the filter to tables only.

    SELECT C.*,T.TABLE_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS C
    left JOIN INFORMATION_SCHEMA.TABLES T on C.TABLE_CATALOG = T.TABLE_CATALOG and T.TABLE_SCHEMA = T.TABLE_SCHEMA and C.TABLE_NAME = T.TABLE_NAME
    WHERE TABLE_TYPE = 'BASE TABLE'

    [Personal Site] [Blog] [Facebook]signature

    Monday, April 28, 2014 10:03 PM
  • Compare those two queries using AdventureWorks2012 database. The fist query with the addition of filter only table bring 104 results while the second query bring 320 results. Moreover I added a simple view to make sure and it was in the result set (before I added my filter).

    Those queries are part of the full solution in the blog:

    		DECLARE @TableToFilter NVARCHAR(100) --= 'test'
    
            SELECT
                C.TABLE_SCHEMA,C.TABLE_CATALOG,C.TABLE_NAME,C.COLUMN_NAME
                -- Here we filter the tables that we want to search in.
                ,TableToFilter =  CASE
                    WHEN @TableToFilter IS NULL THEN 1
                    -- If you need to search in several specific tables, then you shold use CLR Split Function or User type,
                    -- and replace the use of "=" filter with the use of "IN" filter
                    WHEN NOT @TableToFilter IS NULL and C.TABLE_NAME = @TableToFilter THEN 1
                    ELSE 0
                END
            FROM INFORMATION_SCHEMA.COLUMNS C
            left JOIN INFORMATION_SCHEMA.TABLES T on C.TABLE_CATALOG = T.TABLE_CATALOG and T.TABLE_SCHEMA = T.TABLE_SCHEMA and C.TABLE_NAME = T.TABLE_NAME
            WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar') and TABLE_TYPE = 'BASE TABLE'
    		
    		
    		SELECT
                C.TABLE_SCHEMA,C.TABLE_CATALOG,C.TABLE_NAME,C.COLUMN_NAME
                -- Here we filter the tables that we want to search in.
                ,TableToFilter =  CASE
                    WHEN @TableToFilter IS NULL THEN 1
                    -- If you need to search in several specific tables, then you shold use CLR Split Function or User type,
                    -- and replace the use of "=" filter with the use of "IN" filter
                    WHEN NOT @TableToFilter IS NULL and C.TABLE_NAME = @TableToFilter THEN 1
                    ELSE 0
                END
            FROM INFORMATION_SCHEMA.COLUMNS C
            --left JOIN INFORMATION_SCHEMA.TABLES T on C.TABLE_CATALOG = T.TABLE_CATALOG and T.TABLE_SCHEMA = T.TABLE_SCHEMA and C.TABLE_NAME = T.TABLE_NAME
            WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar') --and TABLE_TYPE = 'BASE TABLE'

    * I added your idea of using "COLLATE DATABASE_DEFAULT" in order to make sure that it will work on any database, as well as the view filter.

    This is the code now (I re-write the code in the blog already):

    use AdventureWorks2012
    GO
     
    DECLARE @MyQuery NVARCHAR(MAX) = N''
      
    -- What do we want to find?
    -- If you need to search Multiple Strings then you shold use CLR Split Function as mentioned below*!
    DECLARE @SearchString nvarchar(100) = N'Ariely Ronen'
     
    -- Where do we want to look for?
    -- If you want to search all tables then dont insert any value (NULL),
    -- If you want to search a specific table then insert the table name
    -- If you need to search several specific tables then you should use tables comma delimited as mentioned below!
    DECLARE @TableToFilter NVARCHAR(100) --= 'test'
      
    SET @MyQuery = @MyQuery + (
        SELECT
            'SELECT * FROM (' + CHAR(10) +
            'SELECT ' +
                '''[' + TABLE_CATALOG + ']'' as TABLE_CATALOG, ''[' + TABLE_SCHEMA + ']'' as TABLE_SCHEMA, ''[' + TABLE_NAME + ']'' as TABLE_NAME,' +
                '''[' + COLUMN_NAME + ']'' as COLUMN_NAME, ' +
                 QUOTENAME(COLUMN_NAME) + ' COLLATE DATABASE_DEFAULT as Value' +
            ' FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' +
            -- Here we filter the search String we want to find.
            -- If you need to search Multiple Strings then you shold use CLR Split Function or User type,
            -- and replace the use of "Like" filter with the use of "IN" filter
            ' WHERE [' + TABLE_NAME + '].[' + COLUMN_NAME + '] LIKE ''%'' + @SearchString + ''%'' COLLATE DATABASE_DEFAULT' +
            ') as [' + TABLE_CATALOG + TABLE_SCHEMA + TABLE_NAME + ']' +  CHAR(10) +
            CHAR(10) + 'UNION ALL' + CHAR(10)
        FROM (
            SELECT
                C.TABLE_SCHEMA,C.TABLE_CATALOG,C.TABLE_NAME,C.COLUMN_NAME
                -- Here we filter the tables that we want to search in.
                ,TableToFilter =  CASE
                    WHEN @TableToFilter IS NULL THEN 1
                    -- If you need to search in several specific tables, then you shold use CLR Split Function or User type,
                    -- and replace the use of "=" filter with the use of "IN" filter
                    WHEN NOT @TableToFilter IS NULL and C.TABLE_NAME = @TableToFilter THEN 1
                    ELSE 0
                END
            FROM INFORMATION_SCHEMA.COLUMNS C
            left JOIN INFORMATION_SCHEMA.TABLES T on C.TABLE_CATALOG = T.TABLE_CATALOG and T.TABLE_SCHEMA = T.TABLE_SCHEMA and C.TABLE_NAME = T.TABLE_NAME
            WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar') and TABLE_TYPE = 'BASE TABLE'
        ) T1
        where TableToFilter = 1
        FOR XML PATH('')
    )
    SET @MyQuery = REPLACE(@MyQuery + N'END','UNION ALL'+ CHAR(10) +'END','')
    PRINT @MyQuery
    EXECUTE sp_executesql @MyQuery, N'@SearchString NVARCHAR(100)', @SearchString = N'Glenside'


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Tuesday, April 29, 2014 8:48 AM
    Tuesday, April 29, 2014 8:46 AM
  • Correction - it should be not a LEFT JOIN but the INNER JOIN with the INFORMATION_SCHEMA.Tables.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, April 29, 2014 2:51 PM
  • I added new article which is still under in writing stages

    * i have post three articles this month for three different categuries by the way.


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, April 30, 2014 1:25 PM
  • The correct term would be 'under construction'. 



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, April 30, 2014 2:36 PM
  • Thanks Naomi, 

    since this is temporary, i will fix it when I will continue the article, and I will use the currect term next time. I will continue writing in a few hours... It will be ready tommorow for proofreading, and for other people that want to edit the article.


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, April 30, 2014 2:48 PM