.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

generate Table script using TSQL Sql Server

Posted By: King     Posted Date: September 24, 2012    Points: 2   Category :Sql Server
This code sample helps you to generate the Table script for the particular table. You can create the function and can generate the script by calling the function by passing the Table name as a parameter.

View Complete Post

More Related Resource Links

generate create script of table using c# and SQL server 7.0, can anyone help me?


I want to generate create table script using c#.net, I want to connect sql server 7.0 and generate table create script. Its urgent kindly help me urgently.

SMO classes to generate alter table script to add columns

How to generate ALTER TABLE script to add columns using SMO and c#.

Adding a new field to a table via script in sql server management studio

I know how you can script to and create a new table but how about adding a new field to this table? For example: how would i add a new varchar(50) field named mynewfield?

USE [example]
/****** Object: Table [dbo].[Password]  Script Date: 10/26/2010 10:17:30 ******/
CREATE TABLE [dbo].[Password](
	[user_id] [int] NOT NULL,
	[password] [varchar](30) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
	[response1] [varchar](40) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
	[response2] [varchar](40) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
	[response3] [varchar](40) COLLATE SQL_Latin1_General_CP850_BIN2 NULL,
	[user_id] ASC

ALTER TABLE [dbo].[Password] WITH CHECK ADD CONSTRAINT [User_Password_FK1] FOREIGN KEY([user_id])
REFERENCES [dbo].[User] ([id])
ALTER TABLE [dbo].[Password] CHECK CONSTRAINT [User_Password_FK1]

Cannot generate table scripts with DateTime2 datatype in SQL Server 2008


When trying to generate a "CREATE TO" script from a SQL Server 2008 table containing either DATE or DateTime2 data types,  I get the following error:

Script failed for Table 'dbo.tMyTable'.  (Microsoft.SqlServer.Smo)
Column SnapshotDate in object tMyTable contains type DateTime2, which is not supported in the target server version, SQL Server 2005. (Microsoft.SqlServer.Smo)

However, I've checked all my databases (including master, tempdb, etc.) and ALL are cmptlevel=100.


Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Bulk Copy From Datatable To SQL Server Table


 I have read the data of a excle file and captured the data into the dataset to a datatable, now that data is to be inserted into a SQL Server table using bulk copy option. I am using -

 public bool BulkEnterData(DataTable dt, string tblName)
        SqlBulkCopy bulk = new SqlBulkCopy(con);
        bulk.DestinationTableName = tblName;

        return true;

ERROR Getting-
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) 

How to resolve the above problem or any other solution is available.


Under the Table: Programming with FileStreams in SQL Server 2008


There's always been disagreement about whether large blobs, such as document and multimedia items, should be stored in the database or file system. In SQL Server 2008 you don't have to choose; filestream storage provides the best of both approaches.

Bob Beauchemin

MSDN Magazine May 2009

Under The Table: Spatial Data Support In SQL Server 2008


New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local appl

Hi ,From the last few days I am struggling with the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed."I hav created an asp.net web application with membership shcema. It works fine an my developement machine but when I published it on my web server. It is giving me above error for which I searched but not found any help.Can anyone help me with this.

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

sql server 2005 express - don't allow to create table

i have downloaded sql server 2005 Express version from the web and use domainname\SQLEXPRESS as server name but now i can't create table seems every create by sa user, what is the password of this user or how to grand right to my window login user so that i can modify/create something in database.

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

Triggering an event to occur within a VB.NET app if something is inserted into an SQL Server table

Let's say SQL Server is inserting arow within a table. Is there a way to let VB.NET know that an event was fired (being the inserting of the row within the table), without the need to query anything. Bascially having SQL Server tell the VB.NET app that an event was fired. It seems like it might be possible using CLR and the .NET framework integration wthin SQL Server, but I haven't found a sample program showing what I'm attempting. I understand that this topic covers SQL Server and VB.NET.  This need being for both and this forum only set for SQL Server, I thought I'd take the risk and post in this forum. Thank you in advance for any assistance.Newbie

How to create a view in my database from a table in another server/database

Is there a way to create a view in my database referencing a table in another server/database if I have the connection information.  I know how to do it in Oracle, simply create a database link, but have never done so in sqlserver.

SQL Server Powershell script error!

Hi, I get the following error while trying to execute a powershell script. New-Object : Cannot find type [Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection]: make sure the assembly containing this type is loaded. I'm trying to implement policies across different sql server instances from a primary instance. The script can be found @ http://blogs.msdn.com/sqlpbm/archive/2008/06/14/running-against-sql-server-2005-and-sql-server-2000.aspx Thanks for your help.

Generate Script for Database using WMI script

Hi All, I need to take "database script" or generate script for database by using scheduled job or by SSIS package.   Currently we are doing it manually by selecting "database" --> "Generate Script".  But i need to do it by scheduling a job say for every month and copy it in a particular folder. Is there any way to do it? if yes, what is the methodologies to accomplished it Thanks R Anand

Powershell script that uses Microsoft.SqlServer.Management.Smo.Server need to use Sql Server Autheni

I have written a powershell script that uses Microsoft.SqlServer.Management.Smo.Server.  I have a couple of instances that are on different domains that I must use Sql Server authenication.  I am going to create a report user "sssmorpt".  What is the least a can grant this user to allow me to use Microsoft.SqlServer.Management.Smo.Server to get "$dbsize = $db.size - ($db.logfiles|measure-object -property size -sum).sum / 1024" for all databases in an instances?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend