Execute parameterized batch file through SSIS package with multiple parameter

Hi Folks,

Last week i got stuck with a question in my mind, it’s very usual to run a SSIS Package through the .batch file but can we run a .batch file through SSIS package? The answer is “yes”. yes we can execute batch file through SSIS package. i know it’s very easy and can easily find this on Google. after that i was stuck with some more curious questions can we pass parameter to a batch file? it’s also easy to Google and got the answer “YES”. we can pass upto 9 parameters to a batch file.

Now i give one up to my requirements, So here we are going to move multiple files from different-different location and pass the variable to SSIS package for source location to destination location. like we have 3 files on different source location F: , F:\FirstFile\ and in F:\SecondFile\, and I want to move all these 3 files to F:\DestinationFolder\.

So first we create a batch file:

For Move a file the command is : move F:\SecondFile\second.txt F:\DestinationFolder\

But here we are going to pass parameters to the batch file and we can pass up to 9 parameters to a batch file. for more details about batch file parameter the below link will help you.

Batch file with different type of parameters

So when you call a batch file, you can enter data after the command that the batch file refers to as %1, %2, etc. For example, in the batch file Testlocation.bat, the following command

@echo move %1 %2 file transfer 

would output

move F:\SecondFile\second.txt F:\DestinationFolder\ file transfer 

if you called it as

Testlocation.bat F:\SecondFile\second.txt F:\DestinationFolder\

Now create a table which has a source location column and destination location column for two parameters,

CREATE TABLE FilePaths (
	ID INT Identity
	,SourceFolder VARCHAR(4000)
	,TargetFolder VARCHAR(4000)
	)
INSERT [dbo].[FilePaths] (
	[DashBoard]
	,[SourceFolder]
	,[TargetFolder]
	)
VALUES (
	N'a'
	,N'F:\first.txt'
	,N'F:\DestinationFolder\'
	)
GO

INSERT [dbo].[FilePaths] (
	[DashBoard]
	,[SourceFolder]
	,[TargetFolder]
	)
VALUES (
	N'b'
	,N'F:\SecondFile\second.txt'
	,N'F:\DestinationFolder\'
	)
GO

INSERT [dbo].[FilePaths] (
	[DashBoard]
	,[SourceFolder]
	,[TargetFolder]
	)
VALUES (
	N'c'
	,N'F:\ThirdFile\Third.txt'
	,N'F:\DestinationFolder\'
	)
GO

Now we are going to create a SSIS Package for this:

First create some variable

image

Drag and drop a Execute SQL task

image

Now add the result set to this SQL Task. IDObject which will store all the ID values from the table.

Than pass this result set values to for Each loop, in that for each loop we will convert each value from IDobject into LONG value in a Execute SQL task.

and pass the SQL statement  SELECT  +'”‘+SourceFolder+'”‘, ‘”‘ + TargetFolder+'”‘ FROM RoambiBatchFileInfo WHERE ID = ?

Also we need to pass the result set with Single Row as shown in below screen shot

image

Now we reached to our last and main part of this package where we are going to execute the batch file,

first drag and drop Execute Process task and pass the path of batch file in Executable:

image

Left the argument field blank here and pass the argument through the Expression:

pass the variable through to expression argument with concatenation and add a space in between. like shows in below screenshot.

image

Finally our SSIS package look like the below screen shot:

image

Now execute the package and after completion the package files are moved to the destination folder.

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Advertisements

SSIS Architecture

  • Packages – A package is a collection of tasks framed together with precedence constraints to manage and execute tasks in an order. It is compiled in a XML structured file with .dtsx extension.
  • Control Flow – It acts as the brain of a package.It consists of one or more tasks and containers that executes when package runs. Control flow orchestrates the order of execution for all its components.
  • Tasks – A task can best be explained as an individual unit of work.
  • Precedence Constraints – These are the package components that connect the tasks together and manage the order in which the tasks will execute.
  • Host Containers – The Control flow tab (window) is itself host Container.
  • Containers – Core units in the SSIS architecture for grouping tasks together logically into units of work are known as Containers.
  • Connection Managers – Connection managers are used to centralize connection strings to data sources and to abstract them from the SSIS packages. Multiple tasks can share the same Connection manager.
  • Data Flow – The core strength of SSIS is its capability to extract data into the server’s memory (Extraction), transform it(Transformation) and write it out to an alternative destination(Loading).
  • Sources – A source is a component that you add to the Data Flow design surface to specify the location of the source data.
  • Transformations – Transformations are key components within the Data Flow that allow changes to the data within the data pipeline.
  • Destinations – Inside the Data Flow, destinations consume the data after the data pipe leaves the last transformation components.
  • Variables – Variables can be set to evaluate to an expression at run time.
  • Parameters – Parameters behave much like variables but with a few main exceptions.
  • Event Handlers – The event handlers that run in response to the run-time events that packages, tasks, and containers raise.
  • Log Providers – Logging of package run-time information such as the start time and the stop time of the package and its tasks and containers.
  • Package Configurations – After development your package and before deploying the package in production environment from UAT you need to perform certain package configurations as per production Server.

 

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Difference between DTS and SSIS

The DTS and SSIS are both used in SQL Server Transformations. The DTS was used for transformation purpose up to SQL Server 2000 and SSIS (Including SSAS and SSRS) are the advanced to DTS and are included in SQL Server 2005 onwards. The capability of SSIS tool in comparison with DTS is tremendous. This SSIS tool is in competition with other ETL Tools in the present. The SSIS has lot added features as per the current requirements.

DTS:

· DTS stands for Data Transformation Services.

· DTS is a set of objects using an ETS tool to extract, transform, and load information to or from a database

· DTS was originally part of the Microsoft SQL Server 2000

· Uses Active-x Script

· No Deployment wizard is available

· Limited Set of Transformation available

· Does not support BI Functionality

· Single Task at a time

· It is unmanaged script

· DTS can develop through Enterprise manager

· We can deploy only at local server

· Designer contains Single Pane

· No Event Hander

· No Solution Explorer

· Connection and other values are static, not controlled at runtime.

SSIS:

· SSIS stands for Sql Server Integration Services

· SSIS is an ETL tool provided by Microsoft to extra data from different sources.

· SSIS is a component of the Microsoft SQL Server 2005

· Uses Scripting Language

· Deployment wizard is available

· Huge of Transformations available

· Completely supports end to end process of BI

· Multi Tasks run parallel

· It is managed by CLR

· SSIS can develop through Business Intelligence Development Studio (BIDS, nothing but new version of VS IDE)

· It can be deployed using multiple server using BIDS

· SSIS designer contains 4 design panes:
a) Control Flow
b) Data Flow
c) Event Handlers &
d) Package Explorer.

· Event Handler Available

· Solution Explorer is available, with packages, connections and Data Source Views (DSV)

· It can be controlled dynamically using configuration

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Need of SSIS

List of things SSIS does that SQL server either does poorly or not at all:

  • Connections to a vast number of datasources – flat files, other RDMS systems, XML sources, etc.
  • Complex error handling within dataflows – start and stop dataflows based on severity of the error. Throw an email to admins/DBAs when on error. Pick up the dataflow mid-stream after the error has been resolved.
  • Dataflow auditing – Easily check and redirect data that does not meet/match certain criteria – get an email when a certain number of rows in a given process have no matches found. Easily collect statistics on dataflow processes
  • Visual/logical representation of dataflows as well as logical modularity of dataflows

Way too often, we hear many misconceptions about what the Integration Services Service actually does. Some think it is required to execute packages. Others think it is used for checkpoint restarts. Others think it speeds up execution. So, which group is correct?

The answer is none of them. The SSIS Service, quite simply, is responsible for managing the Integration Services interface in SQL Server Management Studio. It enables the ability to import/export packages, view running packages, and view stored packages. It really doesn’t do anything more than that.

I guess it depends on what you are doing. SSIS is very powerful, just like old DTS. If you are loading lots of items and expect to have constant change, I would go SSIS all the way. If you are looking to load only a few items and it’s for lots of customers, I would put it in code. I prefer SSIS for in house ETL processes, but I use .Net at client shops when I need to load data from a legacy system into a SQL database. Now as I stated before if you have a lot of transformations and lots of different data silos to load, I think you would be crazy to do this in .Net and I would go SSIS. If you have only a few items to load and it’s for a single application and may be installed as part of an application at various clients, I would go .Net all the way. Just my 2 cents.

I don’t think you are entirely wrong, however you seem a bit misguided. SSIS is not intended to replace the Database Engine but meant to compliment it and enhance it. It’s very effective as an ETL tool. For instance, let’s say you wanted to extract a list of clients from one of your database systems and email that list of data to a Direct Mail house every week. With SSIS this is a very easy thing to put together. There’s an easy means of transferring data from SQL Server to a file (CSV, Excel, etc) and sending an email.

Additionally SSIS is nice in that it has the Script Task which will allow you to write VB Script to do a variety of things. From my history I’ve used it to perform data access and send data from one system to another. This will effectively operate the same as if you were to have an application doing the data transfer… but the great thing about SSIS is that you can deploy the package and schedule it run with the SQL Server Agent.

 

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Dynamic generating insert script with given condition and with only required column for given table

Hi Folks,

Today I am going to share a script by which we can dynamically create insert script with only needed columns and only needed data.
In simple words we can avoid unwanted data, even we can avoid unwanted column from insert list.

Here is example table and insert data:
***** you can directly use script at any point or any situation, it is always safe and did not harm any data or any definition  *****

CREATE TABLE [dbo].[Calender](
    [years] [varchar](5) NULL,
    [MONTHS] [varchar](5) NULL,
    [salesAmount] [int] NULL,
    [avrgsaleperMonth] [float] NULL
)

GO

INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jan’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’FEB’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Mar’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Apr’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’May’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jun’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jul’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Aug’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Sep’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’OCT’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’NOV’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’DEC’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jan’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’FEB’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Mar’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Apr’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’May’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jun’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jul’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Aug’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Sep’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’OCT’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’NOV’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’DEC’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jan’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’FEB’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Mar’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Apr’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’May’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jun’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jul’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Aug’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Sep’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’OCT’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’NOV’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’DEC’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jan’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’FEB’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Mar’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Apr’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’May’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jun’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jul’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Aug’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Sep’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’OCT’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’NOV’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’DEC’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jan’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’FEB’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Mar’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Apr’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’May’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jun’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jul’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Aug’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Sep’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’OCT’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’NOV’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’DEC’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′

Now this is the script for generate script of what we want excluding all unwanted column and with only required as we can pass these values in our parameters(@NonIncludeColumnName, @Condition)
as describe below just execute the script and you got the insert script of newly created table (Calender) with only where month = Jan and AvrgSalePerMonth is not null,
And with exclude the column AvrgSalePerMonth and month.

Script :

DECLARE
/***** TableName ****/
@TableName SYSNAME =’Calender’
/***** SchemaName ****/
,@SchemaName SYSNAME =’dbo’ 
/****** JUST PASS COMMA SEPRATED VALUES OF ALL DON’T WANT TO INCLUDE  COLUMN ********/
,@NonIncludeColumnName varchar(250) = ‘avrgsaleperMonth,months’
/***** Where Condition/left blank(”) if don’t want any filtering  ****/
,@Condition VARCHAR(500) = ‘[MONTHS]=”Jan” and avrgsaleperMonth is not null’

    DECLARE @ColumnList VARCHAR(max)
    DECLARE @InsertList VARCHAR(max)
    DECLARE @pkList VARCHAR(max)
    DECLARE @SQL varchar(max)
    DECLARE @WhereSQL varchar(max)
    SELECT @ColumnList = ”
    SELECT @InsertList = ”
    SELECT @pkList  = ” 

/************** Collect NonIncluding Columns **************************/
DECLARE @delimiter nchar(1) = ‘,’

DECLARE @result TABLE
    ([id] int NOT NULL
    ,[value] nvarchar(4000))

IF ISNULL(@NonIncludeColumnName, ”) = N” OR ISNULL(@delimiter, N”) = N”
       OR ISNULL(CHARINDEX(@delimiter, @NonIncludeColumnName, 1), 0) = 0
    BEGIN
        — No valid delimiter and/or csv, so return only the csv value as it.
        INSERT INTO @result ([id], [value]) VALUES (1, @NonIncludeColumnName);
        RETURN;
    END;
    — Csv must end with the delimiter:
    IF LEN(@NonIncludeColumnName) < 4000 AND RIGHT(@NonIncludeColumnName, 1) <> @delimiter
        SET @NonIncludeColumnName = @NonIncludeColumnName + @delimiter;
    — Split the csv value with a CTE
    WITH cte ([id], [value], [leftCsv])
    AS (SELECT 1 as [id]
              ,SUBSTRING(@NonIncludeColumnName
                        ,1
                        ,CHARINDEX(@delimiter, @NonIncludeColumnName, 1) – 1) AS [value]
              ,SUBSTRING(@NonIncludeColumnName
                        ,CHARINDEX(@delimiter, @NonIncludeColumnName, 1) + 1
                        ,LEN(@NonIncludeColumnName)) AS [leftCsv]
        UNION ALL
        SELECT cte.[id] + 1 AS [id]
              ,SUBSTRING(cte.[leftCsv]
                        ,1
                        ,CHARINDEX(@delimiter, cte.[leftCsv], 1) – 1) AS [value]
              ,SUBSTRING(cte.[leftCsv]
                        ,CHARINDEX(@delimiter, cte.[leftCsv], 1) + 1
                        ,LEN(cte.[leftCsv])) AS [leftCsv]
        FROM cte
        WHERE CHARINDEX(@delimiter, cte.[leftCsv], 1) <> 0)
    INSERT INTO @result ([id], [value])
    SELECT [id], [value]
    FROM cte
    OPTION (MAXRECURSION 4000);

/************** END of Collect NonIncluding Columns **********************/
/**************  Collect all column name for the table ******************/
    SELECT @ColumnList = @ColumnList + CASE 
                                       WHEN ST.name not in (‘timestamp’,’geography’,’geometry’,’hierarchyid’,’image’,’binary’,’varbinary’)
                                       THEN Quotename(SC.name) + ‘,’ 
                                       ELSE ” 
                                       END 
     — Collect all insert statments for the table                                 
          ,@InsertList = @InsertList + CASE WHEN ST.name not in (‘timestamp’,’geography’,’geometry’,’hierarchyid’,’image’,’binary’,’varbinary’) THEN
                                        ”” +  QUOTENAME(sc.name) + ‘=” + ‘  + ‘CASE WHEN ‘ + Quotename(SC.name) 
                                        + ‘ IS NULL THEN ”NULL” ELSE  ”””” +    REPLACE(CONVERT(VARCHAR(MAX),’ + Quotename(SC.name) + ‘),””””,””””””) + ””””  END + ‘ + ‘ ”,” + ‘
                                       ELSE ”
                                       END
      FROM SYS.objects so
      JOIN SYS.columns sc
        ON so.object_id = sc.object_id
      JOIN SYS.types st
        ON sc.user_type_id = st.user_type_id
       AND sc.system_type_id = st.system_type_id
      JOIN SYS.schemas sch
        ON sch.schema_id = so.schema_id
     WHERE SO.name = @TableName 
       AND sch.name = @SchemaName
       AND sc.name  IN ( SELECT Value FROM @result )    /********************Which Column Need To Remove**********************/
     ORDER BY SC.column_id
/******************** ADD WHERE CONDITION **********************/
    SELECT @WhereSQL = CASE 
                       WHEN LTRIM(RTRIM(@Condition)) <> ” THEN ‘ WHERE ‘ + @Condition
                       ELSE ”
                       END
    SELECT @ColumnList =SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1)
    SELECT @InsertList =SUBSTRING(@InsertList,1,LEN(@InsertList)-4) + ””
   — combine all variable and generate the final list
    SELECT @SQL = ‘SELECT ”INSERT INTO ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@Tablename) +  char(10)+  ‘(‘ + @ColumnList + ‘)” + char(10)+  ”SELECT ” + ‘ + @InsertList 
                + ‘ FROM ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@Tablename) 
                + @WhereSQL
/******************** EXECUTE THE STATEMENT **********************/
     –PRINT @sql
    EXEC(@SQL)
/******************** END OF SCRIPT **********************/
For removing the table. from database just execute below given drop table statement.
Drop Table TABLE [dbo].[Calender]

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

All Table’s full information captured in self created tables

Hi Folks,

once upon a time for some kind of optimization, table definition change tracking ,constraints review and for deep dive into all table information of a database, come up with the solution of creating a procedure for fully covered table information including all Columns , all Constraint, belonging FileGroup, Identity_Col of that table, all Indices, owner of that table, foreign key columns and many more information is covered and represent within from database tables within one execution, because system created table is not always shows full information of all table in single query.

On the way of final result we have to cross some simple stages :

For this first we need to create tables for store all information :

Just Execute the below script for creating Schema and Tables

NOTE : it’ll surely not harmful for any database or server but Do not use on production server. this script will create schema and tables and procedure which causes the definition of a database might change.
Schema & Table definition:

CREATE SCHEMA info
GO

***************************TABLE CREATE**********************************

/****** Object:  Table [info].[Table_RefrenceBy_col]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Table_RefrenceBy_col](
    [TableName] [varchar](60) NULL,
    [Table_RefrencedBy_Col] [nvarchar](516) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[RowGUID]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[RowGUID](
    [TableName] [varchar](60) NULL,
    [RowGuidCol] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[owner]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[owner](
    [TableName] [varchar](60) NULL,
    [Name] [varchar](40) NULL,
    [Owner] [varchar](20) NULL,
    [Type] [varchar](30) NULL,
    [Created_datetime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[Index]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Index](
    [TableName] [varchar](60) NULL,
    [index_name] [sysname] NOT NULL,
    [Index_description] [varchar](max) NULL,
    [index_keys] [nvarchar](2078) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[Identity_Col]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Identity_Col](
    [TableName] [nvarchar](60) NULL,
    [Identity] [nvarchar](128) NULL,
    [Seed] [numeric](18, 0) NULL,
    [Increment] [numeric](18, 0) NULL,
    [Not For Replication] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[FileGroup]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[FileGroup](
    [TableName] [varchar](60) NULL,
    [Data_located_on_filegroup] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[Constraint]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Constraint](
    [TableName] [varchar](60) NULL,
    [constraint_type] [nvarchar](146) NULL,
    [constraint_name] [nvarchar](MAX) NULL,
    [delete_action] [nvarchar](9) NULL,
    [update_action] [nvarchar](9) NULL,
    [status_enabled] [varchar](8) NULL,
    [status_for_replication] [varchar](19) NULL,
    [constraint_keys] [nvarchar](MAX) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[Columns]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Columns](
    [TableName] [varchar](60) NULL,
    [Column_name] [nvarchar](128) NULL,
    [Type] [nvarchar](128) NULL,
    [Computed] [varchar](35) NULL,
    [Length] [int] NULL,
    [Prec] [char](8) NULL,
    [Scale] [char](5) NULL,
    [Nullable] [varchar](35) NULL,
    [TrimTrailingBlanks] [varchar](35) NULL,
    [FixedLenNullInSource] [varchar](35) NULL,
    [Collation] [sysname] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

Now you have all tables just need to create procedure and execute,

Procedure definition:

CREATE PROC Info_Table
AS
BEGIN
DECLARE @cnt INT , @loopCnt INT = 1–,@DataBaseName NVARCHAR(776)= DB_NAME()
select @cnt = count(*) from information_schema.tables
DECLARE @objname NVARCHAR(776)
WHILE @loopCnt <=  @cnt
BEGIN
IF OBJECT_ID(‘tempdb..#Objects’) IS NOT NULL
    BEGIN
        DROP TABLE #Objects
    END
select identity (INT,1,1) AS ID , TABLE_SCHEMA+’.’+TABLE_NAME As Tablename INTO #Objects from information_schema.tables
SELECT @objname = Tablename   FROM #objects where ID = @loopCnt

DECLARE @dbname SYSNAME
    ,@no VARCHAR(35)
    ,@yes VARCHAR(35)
    ,@none VARCHAR(35)

SELECT @no = ‘no’
    ,@yes = ‘yes’
    ,@none = ‘none’

DECLARE @Results TABLE (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
    ,ResultsText VARCHAR(8000)
    )

SELECT @dbname = PARSENAME(@objname, 3)

IF @dbname IS NULL
    SELECT @dbname = DB_NAME()
ELSE
    IF @dbname <> DB_NAME()
    BEGIN
        INSERT INTO @Results (ResultsText)
        SELECT ‘the @objname must be local to the current database, cross database queries or 3 part naming conventions are not permitted’

        SELECT *
        FROM @Results
    END

DECLARE @objid INT
DECLARE @sysobj_type CHAR(2)

SELECT @objid = object_id
    ,@sysobj_type = type
FROM sys.all_objects
WHERE object_id = OBJECT_ID(@objname)

— IF NOT IN SYSOBJECTS, TRY SYSTYPES — 
IF @objid IS NULL
BEGIN
    — UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME 
    SELECT @objid = TYPE_ID(@objname)

    — IF NOT IN SYSTYPES, GIVE UP 
    IF @objid IS NULL
    BEGIN
        INSERT INTO @Results (ResultsText)
        SELECT ‘the object_id for ‘ + @objname + ‘ was not found’

        SELECT *
        FROM @Results
    END
END

DELETE
FROM info.[Owner]
WHERE TableName = @objname

INSERT INTO info.OWNER
SELECT @objname TableName
    ,convert(VARCHAR(20), o.NAME) NAME
    ,convert(VARCHAR(20), USER_NAME(OBJECTPROPERTY(object_id, ‘ownerid’))) ‘Owner’
    ,convert(VARCHAR(20), SUBSTRING(v.NAME, 5, 31)) ‘Type’
    ,convert(DATETIME, o.create_date) Created_datetime
FROM sys.all_objects o
    ,master.dbo.spt_values v
WHERE o.object_id = object_id(@objname)
    AND o.type = SUBSTRING(v.NAME, 1, 2) COLLATE database_default
    AND v.type = ‘O9T’
ORDER BY o.NAME ASC

IF EXISTS (
        SELECT *
        FROM sys.all_columns
        WHERE object_id = @objid
        )
BEGIN
    — SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE 
    DECLARE @numtypes NVARCHAR(80)

    SELECT @numtypes = N’tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney’

    DELETE
    FROM info.Columns
    WHERE TableName = @objname

    INSERT INTO info.COlumns
    SELECT @objname
        ,CONVERT(CHAR(50), NAME)
        ,CONVERT(CHAR(16), TYPE_NAME(user_type_id))
        ,CONVERT(CHAR(8), CASE
                WHEN COLUMNPROPERTY(object_id, NAME, ‘IsComputed’) = 0
                    THEN @no
                ELSE @yes
                END)
        ,CONVERT(CHAR(8), CONVERT(INT, max_length))
        ,CONVERT(CHAR(8), CASE
                WHEN CHARINDEX(TYPE_NAME(system_type_id), @numtypes) > 0
                    THEN CONVERT(CHAR(5), COLUMNPROPERTY(object_id, NAME, ‘precision’))
                ELSE ‘     ‘
                END)
        ,CONVERT(CHAR(8), CASE
                WHEN CHARINDEX(TYPE_NAME(system_type_id), @numtypes) > 0
                    THEN CONVERT(CHAR(5), ODBCSCALE(system_type_id, scale))
                ELSE ‘     ‘
                END)
        ,CONVERT(CHAR(8), CASE
                WHEN is_nullable = 0
                    THEN @no
                ELSE @yes
                END)
        ,CONVERT(CHAR(18), CASE COLUMNPROPERTY(object_id, NAME, ‘UsesAnsiTrim’)
                WHEN 1
                    THEN @no
                WHEN 0
                    THEN @yes
                ELSE ‘(n/a)’
                END)
        ,CONVERT(CHAR(20), CASE
                WHEN TYPE_NAME(system_type_id) NOT IN (
                        ‘varbinary’
                        ,’varchar’
                        ,’binary’
                        ,’char’
                        )
                    THEN ‘(n/a)’
                WHEN is_nullable = 0
                    THEN @no
                ELSE @yes
                END)
        ,CONVERT(CHAR(20), ISNULL(collation_name, ‘NULL’))
    FROM sys.all_columns
    WHERE object_id = object_id(@objname)
END –if exists sys.columns   

IF @sysobj_type IN (
        ‘S ‘
        ,’U ‘
        ,’V ‘
        ,’TF’
        )
    AND @objid > 0
BEGIN
    DECLARE @colname SYSNAME

    SELECT @colname = COL_NAME(@objid, column_id)
    FROM sys.identity_columns
    WHERE object_id = @objid

    DELETE
    FROM info.Identity_Col
    WHERE TableName = @objname

    INSERT INTO info.Identity_Col
    SELECT @objname
        ,CONVERT(CHAR(27), ISNULL(@colname, ‘No identity column defined.’))
        ,CONVERT(CHAR(4), IDENT_SEED(@objname))
        ,CONVERT(CHAR(9), IDENT_INCR(@objname))
        ,CONVERT(CHAR(20), COLUMNPROPERTY(@objid, @colname, ‘IsIDNotForRepl’))

    SELECT @colname = NULL

    SELECT @colname = NAME
    FROM sys.columns
    WHERE object_id = @objid
        AND is_rowguidcol = 1

    DELETE
    FROM info.RowGUID
    WHERE TableName = @objname

    INSERT INTO info.RowGUID
    SELECT @objname
        ,CONVERT(CHAR(30), ISNULL(@colname, ‘No rowguidcol column defined.’))

    IF EXISTS (
            SELECT *
            FROM sys.all_parameters
            WHERE object_id = @objid
            )
    BEGIN
        DROP TABLE #TEMP

        SELECT CONVERT(CHAR(50), NAME) Param_Name
            ,CONVERT(CHAR(16), TYPE_NAME(user_type_id)) user_type_id
            ,CONVERT(CHAR(8), max_length) max_length
            ,CONVERT(CHAR(16), CASE
                    WHEN TYPE_NAME(system_type_id) = ‘uniqueidentifier’
                        THEN PRECISION
                    ELSE ODBCPREC(system_type_id, max_length, PRECISION)
                    END) system_type_id
            ,CONVERT(CHAR(16), ISNULL(ODBCSCALE(system_type_id, scale), ”)) scale
            ,CONVERT(CHAR(16), parameter_id) parameter_id
            ,CONVERT(CHAR(50), CONVERT(SYSNAME, CASE
                        WHEN system_type_id IN (
                                35
                                ,99
                                ,167
                                ,175
                                ,231
                                ,239
                                )
                            THEN SERVERPROPERTY(‘collation’)
                        ELSE ”
                        END)) collation
        INTO #temp
        FROM sys.all_parameters
        WHERE object_id = @objid

        SELECT *
        FROM #TEMP
    END
END

IF @sysobj_type IN (
        ‘S ‘
        ,’U ‘
        )
BEGIN
    DELETE
    FROM info.FileGroup
    WHERE TableName = @objname

    IF OBJECT_ID(‘tempdb..#temp2’) IS NOT NULL
    BEGIN
        DROP TABLE #Temp2
    END

    CREATE TABLE #Temp2 (FG VARCHAR(max))

    INSERT INTO #Temp2
    EXEC sys.sp_objectfilegroup @objid

    INSERT INTO info.FileGroup
    SELECT @objname
        ,FG
    FROM #Temp2

    DELETE
    FROM info.[Index]
    WHERE TableName = @objname

    IF OBJECT_ID(‘tempdb..#temp3’) IS NOT NULL
    BEGIN
        DROP TABLE #Temp3
    END

    CREATE TABLE #Temp3 (
        IND_Name SYSNAME
        ,Descr VARCHAR(max)
        ,Keys NVARCHAR(2076)
        )

    INSERT INTO #TEMP3
    EXEC sys.sp_helpindex @objname

    INSERT INTO info.[Index]
    SELECT @objname
        ,*
    FROM #TEMP3

    DELETE
    FROM info.Table_RefrenceBy_col
    WHERE TableName = @objname

    IF EXISTS (
            SELECT *
            FROM sys.foreign_keys
            WHERE referenced_object_id = @objid
            )
        INSERT INTO info.Table_RefrenceBy_col
        SELECT @objname
            ,db_name() + ‘.’ + rtrim(schema_name(ObjectProperty(parent_object_id, ‘schemaid’))) + ‘.’ + object_name(parent_object_id) + ‘: ‘ + object_name(object_id)
        FROM sys.foreign_keys
        WHERE referenced_object_id = @objid
        ORDER BY 1
    ELSE
        RAISERROR (
                15470
                ,- 1
                ,- 1
                ,@objname
                ) — No foreign keys reference table ‘%ls’. 

    /*********************************CONSTRAINT FETCH********************************/

    IF OBJECT_ID(‘tempdb..#spcnsttab’) IS NOT NULL
    BEGIN
        DROP TABLE #spcnsttab
    END

    DECLARE @cnstdes NVARCHAR(4000) — string to build up index desc 
        ,@cnstname SYSNAME — name of const. currently under consideration 
        ,@i INT
        ,@cnstid INT
        ,@cnsttype CHAR(2)
        ,@keys NVARCHAR(2126) –Length (16*max_identifierLength)+(15*2)+(16*3) 

    — Create temp table 
    CREATE TABLE #spcnsttab (
        cnst_id INT NOT NULL
        ,cnst_type NVARCHAR(146) collate database_default NOT NULL — 128 for name + text for DEFAULT 
        ,cnst_name SYSNAME collate database_default NOT NULL
        ,cnst_nonblank_name SYSNAME collate database_default NOT NULL
        ,cnst_2type CHAR(2) collate database_default NULL
        ,cnst_disabled BIT NULL
        ,cnst_notrepl BIT NULL
        ,cnst_delcasc BIT NULL
        ,cnst_updcasc BIT NULL
        ,cnst_keys NVARCHAR(2126) collate database_default NULL — see @keys above for length descr 
        )

    — Check to see that the object names are local to the current database. 
    SELECT @dbname = parsename(@objname, 3)

    IF @dbname IS NULL
        SELECT @dbname = db_name()
    ELSE
        IF @dbname <> db_name()
        BEGIN
            RAISERROR (
                    15250
                    ,- 1
                    ,- 1
                    )
                –return (1) 
        END

    — Check to see if the table exists and initialize @objid. 
    SELECT @objid = object_id(@objname)

    IF @objid IS NULL
    BEGIN
        RAISERROR (
                15009
                ,- 1
                ,- 1
                ,@objname
                ,@dbname
                )
            –return (1) 
    END

    — STATIC CURSOR OVER THE TABLE’S CONSTRAINTS 
    DECLARE ms_crs_cnst CURSOR LOCAL STATIC
    FOR
    SELECT object_id
        ,type
        ,NAME
    FROM sys.objects
    WHERE parent_object_id = @objid
        AND type IN (
            ‘C ‘
            ,’PK’
            ,’UQ’
            ,’F ‘
            ,’D ‘
            ) — ONLY 6.5 sysconstraints objects 
    FOR READ ONLY

    — Now check out each constraint, figure out its type and keys and 
    — save the info in a temporary table that we’ll print out at the end. 
    OPEN ms_crs_cnst

    FETCH ms_crs_cnst
    INTO @cnstid
        ,@cnsttype
        ,@cnstname

    WHILE @@fetch_status >= 0
    BEGIN
        IF @cnsttype IN (
                ‘PK’
                ,’UQ’
                )
        BEGIN
            — get indid and index description 
            DECLARE @indid SMALLINT

            SELECT @indid = index_id
                ,@cnstdes = CASE
                    WHEN @cnsttype = ‘PK’
                        THEN ‘PRIMARY KEY’
                    ELSE ‘UNIQUE’
                    END + CASE
                    WHEN index_id = 1
                        THEN ‘ (clustered)’
                    ELSE ‘ (non-clustered)’
                    END
            FROM sys.indexes
            WHERE object_id = @objid
                AND NAME = object_name(@cnstid)

            — Format keys string 
            DECLARE @thiskey NVARCHAR(131) — 128+3 

            SELECT @keys = index_col(@objname, @indid, 1)
                ,@i = 2

            IF (indexkey_property(@objid, @indid, 1, ‘isdescending’) = 1)
                SELECT @keys = @keys + ‘(-)’

            SELECT @thiskey = index_col(@objname, @indid, @i)

            IF (
                    (@thiskey IS NOT NULL)
                    AND (indexkey_property(@objid, @indid, @i, ‘isdescending’) = 1)
                    )
                SELECT @thiskey = @thiskey + ‘(-)’

            WHILE (@thiskey IS NOT NULL)
            BEGIN
                SELECT @keys = @keys + ‘, ‘ + @thiskey
                    ,@i = @i + 1

                SELECT @thiskey = index_col(@objname, @indid, @i)

                IF (
                        (@thiskey IS NOT NULL)
                        AND (indexkey_property(@objid, @indid, @i, ‘isdescending’) = 1)
                        )
                    SELECT @thiskey = @thiskey + ‘(-)’
            END

            — ADD TO TABLE 
            INSERT INTO #spcnsttab (
                cnst_id
                ,cnst_type
                ,cnst_name
                ,cnst_nonblank_name
                ,cnst_keys
                ,cnst_2type
                )
            VALUES (
                @cnstid
                ,@cnstdes
                ,@cnstname
                ,@cnstname
                ,@keys
                ,@cnsttype
                )
        END
        ELSE
            IF @cnsttype = ‘F ‘
            BEGIN
                — OBTAIN TWO TABLE IDs 
                DECLARE @fkeyid INT
                    ,@rkeyid INT

                SELECT @fkeyid = parent_object_id
                    ,@rkeyid = referenced_object_id
                FROM sys.foreign_keys
                WHERE object_id = @cnstid

                — USE CURSOR OVER FOREIGN KEY COLUMNS TO BUILD COLUMN LISTS 
                — (NOTE: @keys HAS THE FKEY AND @cnstdes HAS THE RKEY COLUMN LIST) 
                DECLARE ms_crs_fkey CURSOR LOCAL
                FOR
                SELECT parent_column_id
                    ,referenced_column_id
                FROM sys.foreign_key_columns
                WHERE constraint_object_id = @cnstid

                OPEN ms_crs_fkey

                DECLARE @fkeycol SMALLINT
                    ,@rkeycol SMALLINT

                FETCH ms_crs_fkey
                INTO @fkeycol
                    ,@rkeycol

                SELECT @keys = col_name(@fkeyid, @fkeycol)
                    ,@cnstdes = col_name(@rkeyid, @rkeycol)

                FETCH ms_crs_fkey
                INTO @fkeycol
                    ,@rkeycol

                WHILE @@fetch_status >= 0
                BEGIN
                    SELECT @keys = @keys + ‘, ‘ + col_name(@fkeyid, @fkeycol)
                        ,@cnstdes = @cnstdes + ‘, ‘ + col_name(@rkeyid, @rkeycol)

                    FETCH ms_crs_fkey
                    INTO @fkeycol
                        ,@rkeycol
                END

                DEALLOCATE ms_crs_fkey

                — ADD ROWS FOR BOTH SIDES OF FOREIGN KEY 
                INSERT INTO #spcnsttab (
                    cnst_id
                    ,cnst_type
                    ,cnst_name
                    ,cnst_nonblank_name
                    ,cnst_keys
                    ,cnst_disabled
                    ,cnst_notrepl
                    ,cnst_delcasc
                    ,cnst_updcasc
                    ,cnst_2type
                    )
                VALUES (
                    @cnstid
                    ,’FOREIGN KEY’
                    ,@cnstname
                    ,@cnstname
                    ,@keys
                    ,ObjectProperty(@cnstid, ‘CnstIsDisabled’)
                    ,ObjectProperty(@cnstid, ‘CnstIsNotRepl’)
                    ,ObjectProperty(@cnstid, ‘CnstIsDeleteCascade’)
                    ,ObjectProperty(@cnstid, ‘CnstIsUpdateCascade’)
                    ,@cnsttype
                    )

                INSERT INTO #spcnsttab (
                    cnst_id
                    ,cnst_type
                    ,cnst_name
                    ,cnst_nonblank_name
                    ,cnst_keys
                    ,cnst_2type
                    )
                SELECT @cnstid
                    ,’ ‘
                    ,’ ‘
                    ,@cnstname
                    ,’REFERENCES ‘ + db_name() + ‘.’ + rtrim(schema_name(ObjectProperty(@rkeyid, ‘schemaid’))) + ‘.’ + object_name(@rkeyid) + ‘ (‘ + @cnstdes + ‘)’
                    ,@cnsttype
            END
            ELSE
                IF @cnsttype = ‘C’
                BEGIN
                    — Check constraint 
                    SELECT @i = 1

                    SELECT @cnstdes = NULL

                    SELECT @cnstdes = TEXT
                    FROM syscomments
                    WHERE id = @cnstid
                        AND colid = @i

                    INSERT INTO #spcnsttab (
                        cnst_id
                        ,cnst_type
                        ,cnst_name
                        ,cnst_nonblank_name
                        ,cnst_keys
                        ,cnst_disabled
                        ,cnst_notrepl
                        ,cnst_2type
                        )
                    SELECT @cnstid
                        ,CASE
                            WHEN parent_column_id <> 0
                                THEN ‘CHECK on column ‘ + col_name(@objid, parent_column_id)
                            ELSE ‘CHECK Table Level ‘
                            END
                        ,@cnstname
                        ,@cnstname
                        ,substring(@cnstdes, 1, 2000)
                        ,is_disabled
                        ,is_not_for_replication
                        ,@cnsttype
                    FROM sys.check_constraints
                    WHERE object_id = @cnstid

                    WHILE @cnstdes IS NOT NULL
                    BEGIN
                        IF @i > 1
                            INSERT INTO #spcnsttab (
                                cnst_id
                                ,cnst_type
                                ,cnst_name
                                ,cnst_nonblank_name
                                ,cnst_keys
                                ,cnst_2type
                                )
                            SELECT @cnstid
                                ,’ ‘
                                ,’ ‘
                                ,@cnstname
                                ,substring(@cnstdes, 1, 2000)
                                ,@cnsttype

                        IF len(@cnstdes) > 2000
                            INSERT INTO #spcnsttab (
                                cnst_id
                                ,cnst_type
                                ,cnst_name
                                ,cnst_nonblank_name
                                ,cnst_keys
                                ,cnst_2type
                                )
                            SELECT @cnstid
                                ,’ ‘
                                ,’ ‘
                                ,@cnstname
                                ,substring(@cnstdes, 2001, 2000)
                                ,@cnsttype

                        SELECT @i = @i + 1

                        SELECT @cnstdes = NULL

                        SELECT @cnstdes = TEXT
                        FROM syscomments
                        WHERE id = @cnstid
                            AND colid = @i
                    END
                END
                ELSE
                    IF (@cnsttype = ‘D’)
                    BEGIN
                        SELECT @i = 1

                        SELECT @cnstdes = NULL

                        SELECT @cnstdes = TEXT
                        FROM syscomments
                        WHERE id = @cnstid
                            AND colid = @i

                        INSERT INTO #spcnsttab (
                            cnst_id
                            ,cnst_type
                            ,cnst_name
                            ,cnst_nonblank_name
                            ,cnst_keys
                            ,cnst_2type
                            )
                        SELECT @cnstid
                            ,’DEFAULT on column ‘ + col_name(@objid, parent_column_id)
                            ,@cnstname
                            ,@cnstname
                            ,substring(@cnstdes, 1, 2000)
                            ,@cnsttype
                        FROM sys.default_constraints
                        WHERE object_id = @cnstid

                        WHILE @cnstdes IS NOT NULL
                        BEGIN
                            IF @i > 1
                                INSERT INTO #spcnsttab (
                                    cnst_id
                                    ,cnst_type
                                    ,cnst_name
                                    ,cnst_nonblank_name
                                    ,cnst_keys
                                    ,cnst_2type
                                    )
                                SELECT @cnstid
                                    ,’ ‘
                                    ,’ ‘
                                    ,@cnstname
                                    ,substring(@cnstdes, 1, 2000)
                                    ,@cnsttype

                            IF len(@cnstdes) > 2000
                                INSERT INTO #spcnsttab (
                                    cnst_id
                                    ,cnst_type
                                    ,cnst_name
                                    ,cnst_nonblank_name
                                    ,cnst_keys
                                    ,cnst_2type
                                    )
                                SELECT @cnstid
                                    ,’ ‘
                                    ,’ ‘
                                    ,@cnstname
                                    ,substring(@cnstdes, 2001, 2000)
                                    ,@cnsttype

                            SELECT @i = @i + 1

                            SELECT @cnstdes = NULL

                            SELECT @cnstdes = TEXT
                            FROM syscomments
                            WHERE id = @cnstid
                                AND colid = @i
                        END
                    END

        FETCH ms_crs_cnst
        INTO @cnstid
            ,@cnsttype
            ,@cnstname
    END –of major loop 

    DEALLOCATE ms_crs_cnst

    — Find any rules or defaults bound by the sp_bind… method. 
    INSERT INTO #spcnsttab (
        cnst_id
        ,cnst_type
        ,cnst_name
        ,cnst_nonblank_name
        ,cnst_keys
        ,cnst_2type
        )
    SELECT c.rule_object_id
        ,’RULE on column ‘ + c.NAME + ‘ (bound with sp_bindrule)’
        ,object_name(c.rule_object_id)
        ,object_name(c.rule_object_id)
        ,m.TEXT
        ,’R ‘
    FROM sys.columns c
    INNER JOIN syscomments m ON m.id = c.rule_object_id
    WHERE c.object_id = @objid

    INSERT INTO #spcnsttab (
        cnst_id
        ,cnst_type
        ,cnst_name
        ,cnst_nonblank_name
        ,cnst_keys
        ,cnst_2type
        )
    SELECT c.default_object_id
        ,’DEFAULT on column ‘ + c.NAME + ‘ (bound with sp_bindefault)’
        ,object_name(c.default_object_id)
        ,object_name(c.default_object_id)
        ,m.TEXT
        ,’D ‘
    FROM sys.columns c
    INNER JOIN syscomments m ON m.id = c.default_object_id
    WHERE c.object_id = @objid
        AND objectproperty(c.default_object_id, ‘IsConstraint’) = 0

    — Now print out the contents of the temporary index table. 
    IF EXISTS (
            SELECT *
            FROM #spcnsttab
            )
    BEGIN
        DELETE
        FROM info.[Constraint]
        WHERE TableName = @objname

        INSERT INTO info.[Constraint]
        SELECT @objname
            ,’constraint_type’ = cnst_type
            ,’constraint_name’ = cnst_name
            ,’delete_action’ = CASE
                WHEN cnst_name = ‘ ‘
                    THEN ‘ ‘
                WHEN cnst_2type IN (‘F ‘)
                    THEN CASE
                            WHEN cnst_delcasc = 1
                                THEN ‘Cascade’
                            ELSE ‘No Action’
                            END
                ELSE ‘(n/a)’
                END
            ,’update_action’ = CASE
                WHEN cnst_name = ‘ ‘
                    THEN ‘ ‘
                WHEN cnst_2type IN (‘F ‘)
                    THEN CASE
                            WHEN cnst_updcasc = 1
                                THEN ‘Cascade’
                            ELSE ‘No Action’
                            END
                ELSE ‘(n/a)’
                END
            ,’status_enabled’ = CASE
                WHEN cnst_name = ‘ ‘
                    THEN ‘ ‘
                WHEN cnst_2type IN (
                        ‘F ‘
                        ,’C ‘
                        )
                    THEN CASE
                            WHEN cnst_disabled = 1
                                THEN ‘Disabled’
                            ELSE ‘Enabled’
                            END
                ELSE ‘(n/a)’
                END
            ,’status_for_replication’ = CASE
                WHEN cnst_name = ‘ ‘
                    THEN ‘ ‘
                WHEN cnst_2type IN (
                        ‘F ‘
                        ,’C ‘
                        )
                    THEN CASE
                            WHEN cnst_notrepl = 1
                                THEN ‘Not_For_Replication’
                            ELSE ‘Is_For_Replication’
                            END
                ELSE ‘(n/a)’
                END
            ,’constraint_keys’ = cnst_keys
        FROM #spcnsttab
        ORDER BY cnst_nonblank_name
            ,cnst_name DESC
    END
    ELSE
        RAISERROR (
                15469
                ,- 1
                ,- 1
                ,@objname
                )

    /*******************************************************************************/
    IF (
            SELECT count(*)
            FROM sysdepends
            WHERE depid = @objid
                AND deptype = 1
            ) = 0
    BEGIN
        RAISERROR (
                15647
                ,- 1
                ,- 1
                ,@objname
                ) — No views with schemabinding reference table ‘%ls’. 
    END
    ELSE
    BEGIN
        SELECT DISTINCT ‘Table is referenced by views’ = obj.NAME
        FROM sys.objects obj
            ,sysdepends deps
        WHERE obj.type = ‘V’
            AND obj.object_id = deps.id
            AND deps.depid = @objid
            AND deps.deptype = 1
        GROUP BY obj.NAME
    END
END
ELSE
    IF @sysobj_type IN (‘V ‘)
        AND @objid > 0
    BEGIN
        — VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID 
        PRINT ‘ ‘

        RAISERROR (
                15469
                ,- 1
                ,- 1
                ,@objname
                ) — No constraints defined 

        PRINT ‘ ‘

        RAISERROR (
                15470
                ,- 1
                ,- 1
                ,@objname
                ) — No foreign keys reference table ‘%ls’. 

        EXEC sys.sp_helpindex @objname
    END
    SET @loopCnt = @loopCnt + 1
    END
END

After execute this you have the procedure in your database.

now need to execute the procedure:
EXEC Info_Table

And Select the tables which we created above

SELECT *
FROM info.Columns

SELECT *
FROM info.[Constraint]

SELECT *
FROM info.FileGroup

SELECT *
FROM info.Identity_Col

SELECT *
FROM info.[Index]

SELECT *
FROM info.OWNER

SELECT *
FROM info.RowGUID

SELECT *
FROM info.Table_RefrenceBy_col

you have all table information in these tables, as name shows of every table there is no need to explain, which table is used for which information.

just enjoy with these few tables with bunch of all info in your hand.

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Scope and lifetime of temporary tables

Hi Folks,

Its bit easy to understand the scope and lifetime of Global temporary table (##), but Local temporary tables (#) create many misconception about its scope and lifetime.

Temporary tables (#) are only available at the current session (current window). But in current session there is also too much misconception.

So what is the exact scope or calling area for a temporary table?

For such type of temporary tables (#) SQL follow the inheritance concept, Like:

Inner function can have all the objects of outer function but outer functions not have permission to use the objects of inner function.

in short way child can use all objects of parents but parents cant do the same for child’s objects.

Means, child can access a Temporary tables (#) created in the parent,

Example :

exec(‘select  * into #temp from MyTable; select * from #temp’)

select * from #temp

The #temp table gets created and executed once as seen in results panel.

Then got an error message in messages panel shows: Invalid object name ‘#temp’.

We see that we created the #temp table in the child session,

so as inheritance rule we can’t cal that temporary table it in the parent session, because a temporary table gets

dropped when it goes out of session/scope which happens after exec() finishes it’s execution

Example:

‘select  * into #temp from MyTable

exec(‘select * from #temp’)

select * from #temp

The #temp table gets created and queried twice as seen in results panel.

Here the #temp table is created in the parent session so the child can access it freely.

The temp table gets dropped when the parent session end or can drop table manually.

Same for the procedure :

Example

create table tabe ([ename] varchar(max))

GO

Create procedure Proc1

as

begin

select top 1 [ename] into #temp from [Emp]

exec NestdProc

End

GO

Create procedure NestdProc

as

begin

insert into tabe

select top 1 t.[ename] from [Emp] B

join #temp t on B.[ename] =t.[ename]

END

And Finally you have a record in newly created table

SELECT * FROM tabe

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |