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.