Get all Tables with Column Names and Data types from SQL Server Database

In this post ,we will generate the script that will return us all the table names with column names and data type of those columns. We often need this information. Let's say we are going to prepare mapping document for load the data from Source Database to Destination database. We can get the list of all tables with column names and data types from Source Database and Destination Database and then paste in Excel and map the required input columns to output columns for ETL Process.

SELECT T.Name                   AS TableName,
       Schema_name(T.schema_id) AS SchemaName,
       C.Name                   AS ColumnName,
       Ty.Name                  AS ColumnDataType,
       C.is_nullable            AS IsNullAble,
       C.is_identity            AS IsIdentity 
FROM   sys.tables T
       INNER JOIN sys.columns C
               ON T.OBJECT_ID = C.OBJECT_ID
       INNER JOIN sys.types Ty
               ON C.system_type_id = Ty.system_type_id 
WHERE  T.is_ms_shipped = 0 
ORDER  BY T.name 

How to find String in All Stored Procedures in SQL Server Database

We often have the situations where we want to find string in all the stored procedures in a database. Some of the situations are listed below

• Find out how many stored procedure are using Cross Database Queries, so we want to search for Database name in all the stored procedures in a database.
• Find out in how many stored procedure a table/view is used in a Database? This can be situation where we are planning to change the name of table/view and want to make sure if any Stored Procedure/s will be effected.
• Find out a column name used in Stored Procedure/s in SQL Server Database.
• Find out some comments in all the Stored Procedure to see who created those Stored Procedure, if Creator name is used in the comments.

and there could be more situations. SQL Server provide us system tables and views those store information and definition of objects for us so we can get information related to the objects such as tables,views,Stored Procedures,Triggers,Functions etc.

System.sql_modules and sys.syscomments system views can be used to dig into the definition of objects and search if any other object is used in the definition or not. Let's suppose if I am looking for comments in objects Creator Name=Aamir. I can use both system views to find out required information by using below queries.


SELECT OBJECT_NAME(OBJECT_ID) AS ObjectName,
       definition             AS ObjectDefinition
 FROM   sys.sql_modules 
WHERE  definition LIKE '%Creator Name=Aamir%'


 SELECT OBJECT_NAME(id) AS ObjectName,
       TEXT AS ObjectDefinition 
FROM   sys.syscomments 
WHERE  TEXT LIKE '%Creator Name=Aamir%' 

What are the differences between Merge Join and Lookup transformations in SSIS?

Regarding Lookup:
If you want to find rows matching in source 2 based on source 1 input and if you know there will be only one match for every input row, then I would suggest to use Lookup operation. An example would be you OrderDetails table and you want to find the matching Order Id and Customer Number, then Lookup is a better option.
Regarding Merge Join:
If you want to perform joins like fetching all Addresses (Home, Work, Other) from Address table for a given Customer in the Customer table, then you have to go with Merge Join because the customer can have 1 or more addresses associated with them.
An example to compare:
Here is a scenario to demonstrate the performance differences between Merge Join and Lookup. The data used here is a one to one join, which is the only scenario common between them to compare.

1. I have three tables named dbo.ItemPriceInfo, dbo.ItemDiscountInfo and dbo.ItemAmount. Create scripts for these tables are provided under SQL scripts section.
2. Tablesdbo.ItemPriceInfo and dbo.ItemDiscountInfo both have 13,349,729 rows. Both the tables have the ItemNumber as the common column. ItemPriceInfo has Price information and ItemDiscountInfo has discount information. Screenshot #2 shows the row count in each of these tables. Screenshot #3 shows top 6 rows to give an idea about the data present in the tables.
3. I created two SSIS packages to compare the performance of Merge Join and Lookup transformations. Both the packages have to take the information from tables dbo.ItemPriceInfo and dbo.ItemDiscountInfo, calculate the total amount and save it to the table dbo.ItemAmount.
4. First package used Merge Join transformation and inside that it used INNER JOIN to combine the data. Screenshots #4 and #5 show the sample package execution and the execution duration. It took 05 minutes 14 seconds 719 milliseconds to execute the Merge Join transformation based package.
5. Second package used Lookup transformation with Full cache (which is the default setting). creenshots #6 and #7 show the sample package execution and the execution duration. It took 11 minutes 03 seconds 610 milliseconds to execute the Lookup transformation based package. You might encounter the warning message Information: The buffer manager has allocated nnnnn bytes, even though the memory pressure has been detected and repeated attempts to swap buffers have failed. Here is a link that talks about how to calculate lookup cache size. During this package execution, even though the Data flow task completed faster, the Pipeline cleanup took lot of time.
6. This doesn't mean Lookup transformation is bad. It's just that it has to be used wisely. I use that quite often in my projects but again I don't deal with 10+ million rows for lookup everyday. Usually, my jobs handle between 2 and 3 millions rows and for that the performance is really good. Upto 10 million rows, both performed equally well. Most of the time what I have noticed is that the bottleneck turns out to be the destination component rather than the transformations. You can overcome that by having multiple destinations. Here is an example that shows the implementation of multiple destinations.
7. Screenshot #8 shows the record count in all the three tables. Screenshot #9 shows top 6 records in each of the tables.

Hope that helps.
SQL Scripts:

CREATE TABLE [dbo].[ItemAmount](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
    [Discount] [numeric](18, 2) NOT NULL,
    [CalculatedAmount] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemAmount] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemDiscountInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Discount] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemDiscountInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemPriceInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [nvarchar](30) NOT NULL,
    [Price] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_ItemPriceInfo] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Screenshot #1:

Screenshot #2:

Screenshot #3:

Screenshot #4:

Screenshot #5:

Screenshot #6:

Screenshot #7:

Screenshot #8:

Screenshot #9:

SSRS - Export multiple pdf file - Report generating 50 student details with 50 page (each student one page- group by student).

Step1. Create one Row Group based on Student.
Step2.Row Group has been created. We can see the Group1 in the Row Group Pane. Right Click on the Group1 column Delete Columns only. Delete Column window comes, Choose Delete column only and click on
Step3.Now you can see the Group1 column is deleted but Group1 is still available in the report that groups data Student wise.
Step4.Go to Group1 property by right click on Group1 in Row grouping pane. In the Group property, go to Page Break Page Break Option Check the box for “Between each instance of a group” and “at the end of group”. Right click on the Tablix go to Tablix property. Tablix property windows comes: Check “Add Page break after” and in column header, check “Repeat header columns on each page.” Step5.After implementing Page Break and Grouping, run the report and export it to PDF.

How can Convert amount into words in SQL

I think this will solve your problem.

1. Function to Convert one Digit Number to words.

CREATE    Function dbo.fConvertDigit(@decNumber decimal)

returns varchar(6)

as

Begin

declare

@strWords varchar(6)

Select @strWords = Case @decNumber

     When '1' then 'One'

     When '2' then 'Two'

     When '3' then 'Three'

     When '4' then 'Four'

     When '5' then 'Five'

     When '6' then 'Six'

     When '7' then 'Seven'

     When '8' then 'Eight'

     When '9' then 'Nine'

     Else ''

end

return @strWords

end

2. Function to convert 2 digit number to words.

CREATE    Function dbo.fConvertTens(@decNumber varchar(2))

returns varchar(30)

as

Begin

declare @strWords varchar(30)

--Is value between 10 and 19?

If Left(@decNumber, 1) = 1

begin

Select @strWords = Case @decNumber

     When '10' then 'Ten'

     When '11' then 'Eleven'

     When '12' then 'Twelve'

     When '13' then 'Thirteen'

     When '14' then 'Fourteen'

     When '15' then 'Fifteen'

     When '16' then 'Sixteen'

     When '17' then 'Seventeen'

     When '18' then 'Eighteen'

     When '19' then 'Nineteen'

end

end

else  -- otherwise it's between 20 and 99.

begin

Select @strWords = Case Left(@decNumber, 1)

     When '0' then '' 

     When '2' then 'Twenty '

     When '3' then 'Thirty '

     When '4' then 'Forty '

     When '5' then 'Fifty '

     When '6' then 'Sixty '

     When '7' then 'Seventy '

     When '8' then 'Eighty '

     When '9' then 'Ninety '

end

Select @strWords = @strWords + dbo.fConvertDigit(Right(@decNumber, 1))

end

--Convert ones place digit.

return @strWords

end

3. Function to convert amt in numbers to words. (Built with the help of above 2 functions)

CREATE function dbo.fNumToWords (@decNumber decimal(12, 2))
returns varchar(300)
As
Begin
Declare
@strNumber varchar(100),
@strRupees varchar(200),
@strPaise varchar(100),
@strWords varchar(300),
@intIndex integer,
@intAndFlag integer

Select @strNumber = Cast(@decNumber as varchar(100))
Select @intIndex = CharIndex('.', @strNumber)
if(@decNumber>99999999.99)
BEGIN
RETURN ''
END
If @intIndex > 0
begin
Select @strPaise = dbo.fConvertTens(Right(@strNumber, Len(@strNumber) - @intIndex))
Select @strNumber = SubString(@strNumber, 1, Len(@strNumber) - 3)
If Len(@strPaise) > 0 Select @strPaise = @strPaise + ' paise'
end
Select @strRupees = ''
Select @intIndex=len(@strNumber)
Select @intAndFlag=2
while(@intIndex>0)
begin
if(@intIndex=8)
begin
  Select @strRupees=@strRupees +dbo.fConvertDigit(left(@decNumber,1))+' Crore '
  Select @strNumber=substring(@strNumber,2,len(@strNumber))
  Select @intIndex=@intIndex-1
 
end
else if(@intIndex=7)
begin
  if(substring(@strNumber,1,1)='0')
  begin
   if substring(@strNumber,2,1)<>'0'
   begin
    if (@strRupees<>NULL and substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0' and @intAndFlag=2 and @strPaise=NULL)
    begin
     Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
     Select @intAndFlag=1
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
    end
   
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
   else
   begin
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
  end
  else
  begin
   if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0'  and @intAndFlag=2 and @strPaise='')
   begin
    Select @strRupees=@strRupees+' and ' + dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
    Select @intAndFlag=1
   end
   else
   begin
    Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
   end
   Select @strNumber=substring(@strNumber,3,len(@strNumber))
   Select @intIndex=@intIndex-2
  end
end
else if(@intIndex=6)
  begin
   if(substring(@strNumber,2,1)<>'0' or substring(@strNumber,3,1)<>'0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and @intAndFlag=2 and @strPaise='')
   begin
   
    if len(@strRupees) <= 0
    begin
     if convert(int,substring(@strNumber,1,1)) = 1
     begin
      Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
      Select @intAndFlag=2
     end
     else
     begin
      Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
      Select @intAndFlag=2
     end
    end
    else
    begin
     if convert(int,substring(@strNumber,1,1)) = 1
     begin
      Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
      Select @intAndFlag=1
     end
     else
     begin
      Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
      Select @intAndFlag=1
     end
    end
   end
   else
   begin
    if convert(int,substring(@strNumber,1,1)) = 1
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
    end
   end
   Select @strNumber=substring(@strNumber,2,len(@strNumber))
   Select @intIndex=@intIndex-1
  end
else if(@intIndex=5)
  begin
   if(substring(@strNumber,1,1)='0')
   begin
    if substring(@strNumber,2,1)<>'0'
    begin
     if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
     begin
      Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
      Select @intAndFlag=1
     end
     else
     begin
      Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
     end
     Select @strNumber=substring(@strNumber,3,len(@strNumber))
     Select @intIndex=@intIndex-2
    end
    else
    begin
     Select @strNumber=substring(@strNumber,3,len(@strNumber))
     Select @intIndex=@intIndex-2
    end
   end
   else
   begin
    if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
    begin
     Select @strRupees=@strRupees+' and '+ dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
     Select @intAndFlag=1
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
    end
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
  end
else if(@intIndex=4)
  begin
   if ( (substring(@strNumber,3,1)<>'0' or substring(@strNumber,4,1)<>'0') and substring(@strNumber,2,1)='0' and  @intAndFlag=2 and @strPaise='')
   begin
    Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
    Select @intAndFlag=1
   end
   else
   begin
   Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
   end
   Select @strNumber=substring(@strNumber,2,len(@strNumber))
   Select @intIndex=@intIndex-1
  end
else if(@intIndex=3)
  begin
   if  substring(@strNumber,1,1)<>'0'
   begin
    Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Hundred '
    Select @strNumber=substring(@strNumber,2,len(@strNumber))
   
    if( (substring(@strNumber,1,1)<>'0' or  substring(@strNumber,2,1)<>'0') and @intAndFlag=2 )
    begin
     Select @strRupees=@strRupees+' and '
     Select @intAndFlag=1
    end
    Select @intIndex=@intIndex-1
   end
   else
   begin
    Select @strNumber=substring(@strNumber,2,len(@strNumber))
    Select @intIndex=@intIndex-1
   end
  end
else if(@intIndex=2)
  begin
   if substring(@strNumber,1,1)<>'0'
   begin
    Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))
    Select @intIndex=@intIndex-2
   end
   else
   begin
    Select @intIndex=@intIndex-1
   end
  end
else if(@intIndex=1)
  begin
   if(@strNumber<>'0')
   begin
    Select @strRupees=@strRupees+dbo.fConvertDigit(@strNumber)
   end
   Select @intIndex=@intIndex-1
   
  end
continue
end
if len(@strRupees)>0 Select @strRupees=@strRupees+ ' rupees '
IF(len(@strPaise)<>0)
BEGIN
if len(@strRupees)>0 Select @strRupees=@strRupees + ' and '
END
Select @strWords = IsNull(@strRupees, '') + IsNull(@strPaise, '')
select @strWords = @strWords + ' only'
Return @strWords
End

SSRS 2008 Column Chart with Line

If you want to create a column chart and have a line across the top showing the numbers in two different ways, it is easy to do in SSRS 2008. Just drag over the data into the data field twice. Right click on the data field on change the chart type to line and the other to column.

Here is a table I used to show this.

I created a Column chart and dragged over the date and numbers fields as shown.

Notice I placed the Number data in the data field twice. Then I right clicked on the data field and select the change chart type option. Then selected line chart.

After I select line chart and click OK the chart looks like the image below. It now has columns and lines on the same chart.

If you have multiple Series and only want to show for one series in the chart you will need alter the visibility. Here is the chart with a new columns I will use for the series.

Refresh the data set and drag over the rep to the series field.

Right Click on the data field and go to series properties...

Then set the visibility expression. Remember to set it to Not equal to, this is setting who to hide, not who to show.

The Chart will show multiple columns and a single line.

Split Function In Sql server

CREATE FUNCTION [dbo].[SDF_SplitString]
(
    @sString nvarchar(2048),
    @cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
    if @sString is null return
    declare @iStart int,
      @iPos int
    if substring( @sString, 1, 1 ) = @cDelimiter 
    begin
     set @iStart = 2
     insert into @tParts
     values( null )
    end
    else 
     set @iStart = 1
    while 1=1
    begin
     set @iPos = charindex( @cDelimiter, @sString, @iStart )
     if @iPos = 0
      set @iPos = len( @sString )+1
     if @iPos - @iStart > 0   
      insert into @tParts
      values ( substring( @sString, @iStart, @iPos-@iStart ))
     else
      insert into @tParts
      values( null )
     set @iStart = @iPos+1
     if @iStart > len( @sString ) 
      break
    end
    RETURN

END

Execute: SELECT Part from [dbo].[SDF_SplitString]('CAAB071C-2B5C-4C8C-836C-1EBCDFE6E5D5,318EBFFE-3FE0-4AED-B47F-DCC880ABEAC6',',')A