Query to Create Dynamically com.Parameters.Add - SQL

Select +'com.Parameters.AddWithValue("@'+COLUMN_NAME+' ", dt_results.Rows[i]["'+COLUMN_NAME+'"]); ' from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YourTableName'


This Gives Output :

                    com.Parameters.AddWithValue("@ItemId", dt_results.Rows[i]["ItemId"]);
                    com.Parameters.AddWithValue("@GlobalId", dt_results.Rows[i]["GlobalId"]);
                    com.Parameters.AddWithValue("@ProductId", dt_results.Rows[i]["ProductId"]);
                    com.Parameters.AddWithValue("@CategoryId", dt_results.Rows[i]["CategoryId"]);
                    com.Parameters.AddWithValue("@CategoryName", dt_results.Rows[i]["CategoryName"]);
                    com.Parameters.AddWithValue("@Location", dt_results.Rows[i]["Location"]);
                    com.Parameters.AddWithValue("@PostalCode", dt_results.Rows[i]["PostalCode"]);
                    com.Parameters.AddWithValue("@Title", dt_results.Rows[i]["Title"]);
                    com.Parameters.AddWithValue("@Price", dt_results.Rows[i]["Price"]);
                    com.Parameters.AddWithValue("@SellingState", dt_results.Rows[i]["SellingState"]);
                    com.Parameters.AddWithValue("@TimeLeft", dt_results.Rows[i]["TimeLeft"]);
                    com.Parameters.AddWithValue("@ShippingType", dt_results.Rows[i]["ShippingType"]);
                    com.Parameters.AddWithValue("@Currency", dt_results.Rows[i]["Currency"]);
                    com.Parameters.AddWithValue("@ShipToLocations", dt_results.Rows[i]["ShipToLocations"]);
                    com.Parameters.AddWithValue("@HandlingTime", dt_results.Rows[i]["HandlingTime"]);

Query to Merge All Columns Separated with comma -SQL Server

This Query Gives all columns in a single column Separated with comma.

SELECT STUFF((SELECT COLUMN_NAME+',' as [text()] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName' for xml path('')),1,0,'')

You can Replace the comma with any symbol by which u want to separate.


'TableName' must be replaced by your table name.

Query to Create Columns & Parameters values for Insert Command in Stored Procedure.-SQL

INSERT INTO TableName(name,address,........) VALUES ('@name','@address',...........)

In the Insert Command, we have to Write a large no. of Columns & parameter from their values comes.It takes a lot of time and energy..

Query to Create Columns and Parameters dynamically on 1 click...

Query to Create Columns,  like :   name,address,..........

SELECT STUFF((SELECT COLUMN_NAME+',' as [text()] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YourTableName' for xml path('')),1,0,'')

This Query gives all comma separated columns



Query To Create Parameters from values comes ,like @name,@address...

SELECT STUFF((SELECT '@'+COLUMN_NAME+',' as [text()] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YourTableName' for xml path('')),1,0,'')


This Query Gives all Comma Separated Parameters


NOTE :  Replace YourTableName with your table name

SQL Query to create Parameters for Stored Procedure

QUERY :

SELECT +'@'+COLUMN_NAME+' '+DATA_TYPE+'('+CAST(CHARACTER_MAXIMUM_LENGTH as varchar)+')'+',' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='yourTableName'


Table contains the columns must replace the 'YourTableName' in above query.

This Query Concatenates the Column Name , Datatype and size with the hard coded symbols like '@' ',' ('  and ')'

This Gives the Output in table form, You can copy amd paste it in stored procedure.


OUTPUT:

@ItemId nvarchar(100),
@GlobalId nvarchar(100),
@ProductId nvarchar(100),
@CategoryId nvarchar(100),
@CategoryName varchar(200),
@Location varchar(200),
@Title varchar(500),
@Price nvarchar(200),
@SellingState varchar(100),
@TimeLeft nvarchar(100),
@ShippingType varchar(200),
@Currency varchar(100),
@ShippingServiceCost nvarchar(200),
@ShipToLocations varchar(200),
@OneDayShippingAvail varchar(50),
@ReturnAccept varchar(50),
@ItemUrl nvarchar(200),
@ListingType varchar(200),
@BidCount varchar(200),
@BuyItNowAvail varchar(50),
@BuyItNowPrice nvarchar(200),
@BestOfferEnable varchar(50),
@GalleryUrl varchar(100),
@PaymentMode varchar(200),

SQL Query to Rename Column of a Table

EXECUTE QUERY TO RENAME THE COLUMN :

 sp_RENAME 'YourTableName.OldColumnName', 'NewColumnName' , 'COLUMN'


 FOR EX:

  sp_RENAME 'Student_details.Division', 'Grade' , 'COLUMN'


This Query, Rename the Column 'Division' to the 'Grade' in the 'Student_details' table.

 Rename Your Column may be Risky..

I got this Caution (below in Red) every time , but i got changes successfully without any data loss.

Caution: Changing any part of an object name could break scripts and stored procedures.




 -----     ---     ----     ---------------------------------------------------------------------------------------------------------
Disclaimer: In case of any loss, would not the responsibility of this blog, Data or Author.

SQL Query to get Column Count & Details of a Database

Get Columns Count of all tables in a Database

Select TABLE_NAME as 'All tables', COUNT(*) as 'no. of columns'
From INFORMATION_SCHEMA.COLUMNS
Group By TABLE_NAME
Order By TABLE_NAME

Total no. of Columns in a Table:

SELECT count(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableHere'           

Columns Details of a Table: 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableHere'

All Columns Names of a Table: 

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableHere'