SQL Server - Generate a script for fields which are part of each table in database

-
 
SQL Server - Generate a script for fields which are part of each table in database

When you need to some fixed fields to be part of each header table in database and always we have to create those field manually by type field name and creating foreign key. We can do this with help of below script. which I found helpful each time in create new table in database.

In my case I had AddOprID , and EditOprID to part of each table and same is foreign key reference to User Master.

 
Declare @UserTable as Varchar(100)
Declare @Schema as Varchar(100)
Declare @Table as Varchar(100)
Declare @SQL as varchar(8000)
 
 
SET @UserTable ='mastUser'
SET @Schema ='dbo'
SET @Table ='TranSales'
 
 
SET @SQL  = 'Alter TABLE [' + @Schema +'].[' + @Table + '] ADD [AddOprID] [int] NOT NULL  ' + Convert(Varchar,CHAR(10)) +
                                                ' Alter TABLE [' + @Schema +'].[' + @Table + '] ADD [AddDate] [datetime] NOT NULL  ' +  Convert(Varchar,CHAR(10)) +
                                                ' Alter TABLE [' + @Schema +'].[' + @Table + '] ADD [EditOprID] [int] NULL ' +  Convert(Varchar,CHAR(10)) +
                                                ' Alter TABLE [' + @Schema +'].[' + @Table + '] ADD [EditDate] [datetime] NULL ' +  Convert(Varchar,CHAR(10)) +
 
' GO ' + Convert(Varchar,CHAR(13)) +
 
' ALTER TABLE [' + @Schema +'].[' + @Table + ']  WITH CHECK ADD  CONSTRAINT [FK_' + @Table + '_'+  @UserTable +'] FOREIGN KEY([AddOprID]) ' +  Convert(Varchar,CHAR(13)) +
' REFERENCES [' + @Schema +'].['+  @UserTable +'] ([ID]) ' +   Convert(Varchar,CHAR(13) ) +
' GO ' +  Convert(Varchar,CHAR(13)) +
' ALTER TABLE [' + @Schema +'].[' + @Table + '] CHECK CONSTRAINT [FK_' + @Table + '_'+  @UserTable +'] ' +  Convert(Varchar,CHAR(13)) +
' GO ' +  Convert(Varchar,CHAR(13)) +
 
' ALTER TABLE [' + @Schema +'].[' + @Table + ']  WITH CHECK ADD  CONSTRAINT [FK_' + @Table + '_'+  @UserTable +'1] FOREIGN KEY([EditOprID]) ' +  Convert(Varchar,CHAR(13)) +
' REFERENCES [' + @Schema +'].['+  @UserTable +'] ([ID]) ' +  Convert(Varchar,CHAR(13)) +
' GO ' +  Convert(Varchar,CHAR(13)) +
 
' ALTER TABLE [' + @Schema +'].[' + @Table + '] CHECK CONSTRAINT [FK_' + @Table + '_'+  @UserTable +'1] ' +  Convert(Varchar,CHAR(13)) +
' GO '  + Convert(Varchar,CHAR(13))
               
  
Select @SQL


OUTPUT

-- -This will create fields in Sales Table 
Alter TABLE [dbo].[TranSales] ADD [AddOprID] [int] NOT NULL    
Alter TABLE [dbo].[TranSales] ADD [AddDate] [datetime] NOT NULL    
Alter TABLE [dbo].[TranSales] ADD [EditOprID] [int] NULL   
Alter TABLE [dbo].[TranSales] ADD [EditDate] [datetime] NULL   


--- Foreign Key Script for the above fields 
GO   
ALTER TABLE [dbo].[TranSales]  WITH CHECK ADD  CONSTRAINT [FK_TranSales_mastUser] FOREIGN KEY([AddOprID])   
REFERENCES [dbo].[mastUser] ([ID])   
GO   
ALTER TABLE [dbo].[TranSales] CHECK CONSTRAINT [FK_TranSales_mastUser]   


GO   
ALTER TABLE [dbo].[TranSales]  WITH CHECK ADD  CONSTRAINT [FK_TranSales_mastUser1] FOREIGN KEY([EditOprID])   
REFERENCES [dbo].[mastUser] ([ID])   
GO   
ALTER TABLE [dbo].[TranSales] CHECK CONSTRAINT [FK_TranSales_mastUser1]   
GO  

 
 

Comments

Post a Comment

Popular posts from this blog

Shared / Static Class in vb.net/C#

-
Image
Shared / Static Class in vb.net/C# VB.net     : Shared C#            : Static You can’t create object of Shared Class Below Image show Properties and Method are not visible for Shared/Static Class Public Class Form1     Private Sub Button1_Click(sender As Object , e As EventArgs ) Handles Button1.Click         'Module is VB.net are Shared Type Class.         'Shared Class Example is ARC.Tools.GRIDManager         'When Class s Defined as Shared There no need to create object of Class.         'If you just want to create a class that you can't inherit, in C# you can use         ' Sealed, and in VB.Net use NotInheritable.         'The VB.Net equivalent of s...
Read more

Xamarin Forms : PopUp Page with RG.Plugins.Popup

-
Image
How to Show PopUp Page In Xamarin Forms Step 1 First, we need to add the Third Page Plugin to achieve the same. [ Rg.Plugins.Popup ] Go to Manage Nuget Packages - > Search for Rg.Plugins.Popup Once the plugin is installed in the Project We need to configure same in Android and IOS Projects In Android   - Go to MainActivity.cs Code:     Rg.Plugins.Popup. Popup .Init( this , savedInstanceState); In IOS –   Go to   - AppDelegate.cs Code : Rg.Plugins.Popup. Popup .Init(); Now Back to Main Project (PCL) Step – 1 :   Add Page in Project Once Added Page Change the Tag of Page Default Page has Content page as Tag which needs to be replaced with PopUpPage Class  :  Default Page:- Class < ContentPage  xmlns ="http://xamarin.com/schemas/2014/forms" Change To < pages : PopupPage  xmlns ="http://xamarin.com/schemas/2014/forms" This is how it should Lo...
Read more

Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

-
Image
  Error While importing Excel Data using SQL Server Import Wizard   - Executing (Error) Messages Error 0xc020901c: Data Flow Task 1: There was an error with Source - Worksheet$.Outputs[Excel Source Output].Columns[Address] on Source - Worksheet$.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".  (SQL Server Import and Export Wizard)   Error 0xc020902a: Data Flow Task 1: The "Source - Worksheet$.Outputs[Excel Source Output].Columns[Address]" failed because truncation occurred, and the truncation row disposition on "Source - Worksheet$.Outputs[Excel Source Output].Columns[Address]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.  (SQL Server Import and Export Wizard)   - Executing (Error) Messages Error 0xc020901c: Data Flow Task 1: There was an error with Source - Worksheet$.Outputs[Excel Source O...
Read more