Inner Vs. Outer Join

What is the difference between and inner and out join in SQL?  I hear this question a lot (at work, when I conduct interviews, and when I'm interviewed).  The answer isn't as straight forward as you may think.  Here is a diagram from the article  "Visual Representation of SQL Joins" by C.L. Moffat that show's a visual representation of what each time of join does:

There are other good discussion on the difference between inner and outer joins found in Stack Overflow.

Search for Column in All Tables

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name,c.max_length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%SomeColumnName%'
ORDER BY schema_name, table_name;


Links
Dave Pinal

Searching Wildcard Character In a Varchar

declare @ExpressionToSearch varchar(100),@ExpressionToFind varchar(1)

set @ExpressionToSearch ='%blah hello%'

set @ExpressionToFind ='[%]'

print len(@ExpressionToSearch)
print substring(@ExpressionToSearch,1,1)
print substring(@ExpressionToSearch,2,len(@ExpressionToSearch))

print substring(@ExpressionToSearch,len(@ExpressionToSearch),1)

print substring(@ExpressionToSearch,1,len(@ExpressionToSearch)-1)

IF @ExpressionToSearch LIKE '%' + @ExpressionToFind + '%'

    PRINT 'Yes it Contains'
       if substring(@ExpressionToSearch,1,1)='%'
              print 'first'
       if substring(@ExpressionToSearch,len(@ExpressionToSearch),1)='%'
              print 'last'
ELSE
    PRINT 'It doesn''t Contain'

Links
stackoverflow
sqlhints

Cannot Resolve the Collation Conflict in WHERE Clause

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

 

 

 

 select * from sometable1

 

 where user_id COLLATE DATABASE_DEFAULT in (

       select userid COLLATE DATABASE_DEFAULT from (

              SELECT distinct convert(nvarchar(100),[User]) [user],[UserId]

              FROM sometable2

         ) a

  )

 

stackoverflow

Counting Number of Columns in a Table

select count(*)
from Information_Schema.Columns c
where table_name = 'mytable'


stackoverflow

Search All Table for Text

I thought I had posted something like this before, but then found  new option for Searching All Table for Text Value - Sorna Kumar Muthuraj

 

EXEC SP_SearchTables @Tablenames = '%'

,@SearchStr = '%some text to search%'

-------------
IF OBJECT_ID('SP_SearchTables','P') IS NOT NULL
    DROP PROCEDURE SP_SearchTables
GO

CREATE PROCEDURE SP_SearchTables

 @Tablenames VARCHAR(500)
,@SearchStr NVARCHAR(60)
,@GenerateSQLOnly Bit = 0
AS
 
/*
Written by Sorna Kumar Muthuraj

    Parameters and usage
 
    @Tablenames        -- Provide a single table name or multiple table name with comma seperated. 
                        If left blank , it will check for all the tables in the database
    @SearchStr        -- Provide the search string. Use the '%' to coin the search. 
                        EX : X%--- will give data staring with X
                             %X--- will give data ending with X
                             %X%--- will give data containig  X
    @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database. 
                        By default it is 0 and it will search.
 
    Samples :
 
    1. To search data in a table
 
        EXEC SP_SearchTables @Tablenames = 'T1'
                         ,@SearchStr  = '%TEST%'
 
        The above sample searches in table T1 with string containing TEST.
 
    2. To search in a multiple table
 
        EXEC SP_SearchTables @Tablenames = 'T2'
                         ,@SearchStr  = '%TEST%'
 
        The above sample searches in tables T1 & T2 with string containing TEST.
    
    3. To search in a all table
 
        EXEC SP_SearchTables @Tablenames = '%'
                         ,@SearchStr  = '%TEST%'
 
        The above sample searches in all table with string containing TEST.
 
    4. Generate the SQL for the Select statements
 
        EXEC SP_SearchTables @Tablenames        = 'T1'
                         ,@SearchStr        = '%TEST%'
                         ,@GenerateSQLOnly    = 1
 
*/
 
    SET NOCOUNT ON
 
    DECLARE @MatchFound BIT
 
    SELECT @MatchFound = 0
 
    DECLARE @CheckTableNames Table
    (
    Tablename sysname
    )
 
    DECLARE @SQLTbl TABLE
    (
     Tablename        SYSNAME
    ,WHEREClause    VARCHAR(MAX)
    ,SQLStatement   VARCHAR(MAX)
    ,Execstatus        BIT 

    )

 
    DECLARE @SQL VARCHAR(MAX)
    DECLARE @tmpTblname sysname
    DECLARE @ErrMsg VARCHAR(100)
 
    IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
    BEGIN
 
        INSERT INTO @CheckTableNames
        SELECT Name
          FROM sys.tables
    END
    ELSE
    BEGIN
 
        SELECT @SQL = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + ''''
 
        INSERT INTO @CheckTableNames
        EXEC(@SQL)
 
    END
 
    IF NOT EXISTS(SELECT 1 FROM @CheckTableNames)
    BEGIN
        
        SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter'
        PRINT @ErrMsg
        RETURN
 
    END
    
    INSERT INTO @SQLTbl
    ( Tablename,WHEREClause)
    SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME),
            (
                SELECT '[' + SC.Name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10)
                  FROM SYS.columns SC
                  JOIN SYS.types STy
                    ON STy.system_type_id = SC.system_type_id
                   AND STy.user_type_id =SC.user_type_id
                 WHERE STY.name in ('varchar','char','nvarchar','nchar','text')
                   AND SC.object_id = ST.object_id
                 ORDER BY SC.name
                FOR XML PATH('')
            )
      FROM  SYS.tables ST
      JOIN @CheckTableNames chktbls
                ON chktbls.Tablename = ST.name 
      JOIN SYS.schemas SCh
        ON ST.schema_id = SCh.schema_id
     WHERE ST.name <> 'SearchTMP'
      GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' +  QUOTENAME(ST.NAME) ;
    
 
      UPDATE @SQLTbl
         SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
 
      DELETE FROM @SQLTbl
       WHERE WHEREClause IS NULL
    
    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
    BEGIN
 
        SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement
          FROM @SQLTbl 
         WHERE ISNULL(Execstatus ,0) = 0
 
         IF @GenerateSQLOnly = 0
         BEGIN
 
            IF OBJECT_ID('SearchTMP','U') IS NOT NULL
                DROP TABLE SearchTMP
                
            EXEC (@SQL)
 
            IF EXISTS(SELECT 1 FROM SearchTMP)
            BEGIN
                SELECT Tablename=@tmpTblname,* FROM SearchTMP
                SELECT @MatchFound = 1
            END
 
         END
         ELSE
         BEGIN
             PRINT REPLICATE('-',100)
             PRINT @tmpTblname
             PRINT REPLICATE('-',100)
             PRINT replace(@SQL,'INTO SearchTMP','')
         END
 
         UPDATE @SQLTbl
            SET Execstatus = 1
          WHERE Tablename = @tmpTblname
 
    END

    IF @MatchFound = 0 
    BEGIN
        SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter'
        PRINT @ErrMsg
        RETURN
    END
    
    SET NOCOUNT OFF
 
go
 
 

Search All Stored Procedures

SELECT DISTINCT o.name AS Object_Name,o.type_desc

FROM sys.sql_modules m

INNER JOIN sys.objects o

ON m.object_id=o.object_id

WHERE m.definition Like '%InsertIds%'

--or

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%InsertIds%'

GO

Stack Overflow

Pinal Dave

Search All Tables

/****** Object:  StoredProcedure [development].[SearchAllTables]    Script Date: 31/01/2014 7:00:58 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [development].[SearchAllTables]

(

    @SearchStr nvarchar(100)

)

AS

BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.

-- Purpose: To search all columns of all tables for a given search string

-- Written by: Narayana Vyas Kondreddi

-- Site: http://vyaskn.tripod.com

-- Tested on: SQL Server 7.0 and SQL Server 2000

-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET  @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

    SET @ColumnName = ''

    SET @TableName =

    (

        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

        FROM    INFORMATION_SCHEMA.TABLES

        WHERE       TABLE_TYPE = 'BASE TABLE'

            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

            AND OBJECTPROPERTY(

                    OBJECT_ID(

                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

                         ), 'IsMSShipped'

                           ) = 0

    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN

        SET @ColumnName =

        (

            SELECT MIN(QUOTENAME(COLUMN_NAME))

            FROM    INFORMATION_SCHEMA.COLUMNS

            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)

                AND TABLE_NAME  = PARSENAME(@TableName, 1)

                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

                AND QUOTENAME(COLUMN_NAME) > @ColumnName

        )

        IF @ColumnName IS NOT NULL

        BEGIN

            INSERT INTO @Results

            EXEC

            (

                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

                FROM ' + @TableName + ' (NOLOCK) ' +

                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

            )

        END

    END

END

SELECT ColumnName, ColumnValue FROM @Results

END