this is a sql script to genrate table script
declare @vsSQL varchar(8000)
declare @vsTableName varchar(50)
select @vsTableName = '_PRODUCT'--- Your Table Name here
select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10)
select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' +
st.Name +
case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end +
case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10)
from sysobjects so
join syscolumns sc on sc.id = so.id
join systypes st on st.xusertype = sc.xusertype
where so.name = @vsTableName
order by
sc.ColID
select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'
Edit: c# code
public string GetScript(string strConnectionString
, string strObject
, int ObjType)
{
string strScript = null;
int intCounter = 0;
if (ObjType != 0)
{
ObjSqlConnection = new SqlConnection(strConnectionString.Trim());
try
{
ObjDataSet = new DataSet();
ObjSqlCommand = new SqlCommand("exec sp_helptext
[" + strObject + "]", ObjSqlConnection);
ObjSqlDataAdapter = new SqlDataAdapter();
ObjSqlDataAdapter.SelectCommand = ObjSqlCommand;
ObjSqlDataAdapter.Fill(ObjDataSet);
foreach (DataRow ObjDataRow in ObjDataSet.Tables[0].Rows)
{
strScript += Convert.ToString(ObjDataSet.Tables[0].Rows[intCounter][0]);
intCounter++;
}
}
catch (Exception ex)
{
strScript = ex.Message.ToString();
}
finally
{
ObjSqlDataAdapter = null;
ObjSqlCommand = null;
ObjSqlConnection = null;
}
}
return strScript;
}
To create Insert script use this store procedure
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[InsertGenerator]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[InsertGenerator]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[InsertGenerator]
(
@tableName varchar(100),
@KeyColumn1 varchar(100)='',
@KeyColumn2 varchar(100)=''
)
AS
-- Generating INSERT statements in SQL Server
-- to validate if record exists - supports 2 field Unique index
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(max) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(max) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
DECLARE @FieldVal nvarchar(1000) -- save value for the current field
DECLARE @KeyVal nvarchar(1000) -- save value for the current field
DECLARE @KeyTest0 nvarchar(1000) -- used to test if key exists
DECLARE @KeyTest1 nvarchar(1000) -- used to test if key exists
DECLARE @KeyTest2 nvarchar(1000) -- used to test if key exists
SET @KeyTest0=''
IF @KeyColumn1<>''
SET @KeyTest0='IF not exists (Select * from '+@tableName
SET @KeyTest1=''
SET @KeyTest2=''
SET @string='INSERT '+@tableName+'('
SET @stringData=''
SET @FieldVal=''
SET @KeyVal=''
DECLARE @colName nvarchar(50)
FETCH NEXT FROM cursCol INTO @colName,@dataType
IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END
WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
SET @FieldVal=''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
SET @KeyVal='''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
SET @stringData=@stringData+@FieldVal
END
ELSE
if @dataType in ('text','ntext','xml') --if the datatype is text or something else
BEGIN
SET @FieldVal='''''''''+isnull(cast('+@colName+' as varchar(max)),'''')+'''''',''+'
SET @stringData=@stringData+@FieldVal
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @FieldVal='''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+@FieldVal
END
ELSE
IF @dataType='datetime'
BEGIN
SET @FieldVal='''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+@FieldVal
END
ELSE
IF @dataType='image'
BEGIN
SET @FieldVal='''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
SET @stringData=@stringData+@FieldVal
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
SET @FieldVal=''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
SET @KeyVal='''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
SET @stringData=@stringData+@FieldVal
END
--Build key test
IF @KeyColumn1=@colName
begin
SET @KeyTest1 = ' WHERE [' + @KeyColumn1 + ']='
SET @KeyTest1 = @KeyTest1+@KeyVal+']'
end
IF @KeyColumn2=@colName
begin
SET @KeyTest2 = ' AND [' + @KeyColumn2 + ']='
SET @KeyTest2 = @KeyTest2+@KeyVal+']'
end
SET @string=@string+'['+@colName+'],'
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(max)
-- Build the test string to check if record exists
if @KeyTest0<>''
begin
if @Keycolumn1<>''
SET @KeyTest0 = @KeyTest0 + substring(@KeyTest1,0,len(@KeyTest1)-4)
if @Keycolumn2<>''
begin
SET @KeyTest0 = @KeyTest0 + ''''
SET @KeyTest0 = @KeyTest0 + substring(@KeyTest2,0,len(@KeyTest2)-4)
end
SET @KeyTest0 = @KeyTest0 + ''')'
SET @query ='SELECT '''+substring(@KeyTest0,0,len(@KeyTest0)) + ') '
end
else
SET @query ='SELECT '''+substring(@KeyTest0,0,len(@KeyTest0))
SET @query = @query + substring(@string,0,len(@string)) + ') '
SET @query = @query + 'VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
CLOSE cursCol
DEALLOCATE cursCol
GO
and use of InsertGenerator like below
DECLARE @return_value int
EXEC @return_value = [dbo].[InsertGenerator]
@tableName = N'_PRODUCT'
SELECT 'Return Value' = @return_value
