August 17, 2010 at 10:31 am
CRATE procedure [dbo].[Auto_Create_Procedure]
@table_name varchar(200)
AS
DECLARE @column_name varchar(30)
DECLARE @column_length int
--DECLARE @table_name varchar(30)
DECLARE @column_string varchar(4000)
DECLARE @value_string varchar(4000)
DECLARE @Row_Count int
DECLARE @data_type varchar(30)
set @Row_Count = 0
set @column_string = ''
set @value_string = ''
--set @table_name ='tblBook'
DECLARE column_cursor CURSOR FOR
select b.name column_name, b.length, c.name data_type
from sysobjects a, syscolumns b, systypes c
where a.id = b.id and b.xtype = c.xtype and a.name = @table_name and c.name <> 'sysname'
order by colorder
-- Print PROCEDURE command
PRINT 'CREATE PROCEDURE Prc_Insert_' + @table_name + '('
OPEN column_cursor
FETCH NEXT FROM column_cursor
INTO @column_name, @column_length, @data_type
WHILE @@FETCH_STATUS = 0
BEGIN
set @Row_Count = @Row_Count + 1
if(@ROW_COUNT <> @@CURSOR_ROWS)
-- IN TUNG THAM SO RA MAN HINH
PRINT ' @' + @column_name + ' ' + dbo.fnc_get_datatype(@data_type, @column_length) + ', '
else
PRINT ' @' + @column_name + ' ' + dbo.fnc_get_datatype(@data_type, @column_length)
--
set @column_string = @column_string + @column_name + ', '
--
set @value_string = @value_string + '@' + @column_name + ', '
FETCH NEXT FROM column_cursor
INTO @column_name, @column_length, @data_type
END
CLOSE column_cursor;
DEALLOCATE column_cursor;
PRINT ')'
PRINT 'AS'
PRINT 'BEGIN'
-- Print Insert command
print ' INSERT INTO ' + @table_name
print ' (' + SUBSTRING(@column_string, 1, LEN(@column_string) - 1 ) + ') '
print ' VALUES'
print ' (' + SUBSTRING(@value_string, 1, LEN(@value_string) - 1 ) + ')'
PRINT 'END'
PRINT 'GO'
Visit this site to reference SQL command
August 25, 2010 at 7:10 am
Not sure what you question is or if you are just providing a script. The script doesn't work. You need to change 'CRATE' to 'CREATE'. Also, dbo.fnc_get_datatype does not exist
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2012 at 1:30 am
Been around for a long time and well used by a lot of people.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy