通过提供表名自动创建表结构 [英] Autoamtion of creating a table structure by giving a table name

问题描述

您好团队可以帮助我，当创建表名时，是否有用于创建表的自动化代码。所有数据类型和约束等都将在一个单独的表中提供。

a单独的表维护我们有列名，该表的数据类型和所需的约束信息。

当我提供表名时，我需要在SP或任何其他SQL代码创建表，它应该创建具有给定名称的表。



它类似于下面。

Hi Team could you please help me is there any automation code for creating table when table name is given to create. all data types and constraints etc. will be available in a separate table.
a separate table is maintained where we have column names, data types for that table and required constraint information.
I need at SP or any other SQL code to create a table when I give table name, it should create that table with the given name.

It is something like below.

    CREATE PROCEDURE createtable
    (
    @TABLENAME as nvarchar (50)
     )
    AS
BEGIN
	DECLARE 
	--@TABLE_CATALOG nvarchar (50),
	@TABLE_SCHEMA nvarchar (50),
	@TABLE_NAME nvarchar (50),
	@COLUMN_NAME nvarchar (50),
	@DATA_TYPE nvarchar (50),
	@CHARACTER_MAXIMUM_LENGTH nvarchar (50),
    @SQL nvarchar(2000)
    --SET @SQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    --drop table [dbo].[@TABLENAME]
    BEGIN
	
    SELECT @SQL = 'CREATE TABLE ' + @TABLENAME + '('
    SELECT @SQL = @SQL + 'SELECT @TABLE_CATALOG = TABLE_CATALOG,@TABLE_SCHEMA = TABLE_SCHEMA,@TABLENAME = TABLE_NAME,@COLUMN_NAME = COLUMN_NAME,@DATA_TYPE = DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH = CHARACTER_MAXIMUM_LENGTH FROM information_schema.columns WHERE TABLE_NAME = @TABLENAME)'
	
    --CONSTRAINT [PK_FinancialPeriod' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED(
    --[FinPeriodNr] ASC
	
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	EXEC(@SQL)
	END
	END
    --GO
    --SET NOCOUNT ON;

推荐答案

你可以动态地在SQL中执行以下操作

You an do that in SQL dynamically as below

Create Table #columnNames(columnName varchar(40),ColumnDataType varchar(40),ColumnWidth varchar(10))
        insert #columnNames select 'col1','varchar','10'
        insert #columnNames select 'col2','int','0'

        Declare @Query varchar(Max),@tableName varchar(20),@ColName varchar(200)
        set @tableName='#Table1'


        select @ColName=Coalesce(@ColName+', ','')+Cast(a.columnName as varchar(20))+' '+
                Cast(a.ColumnDataType as varchar(20))+
                (Case when a.ColumnDataType in ('varchar','nvarchar') then '('+a.ColumnWidth+')' else '' end)
        from #columnNames a

        set @Query='Create Table '+@tableName+' ('+@ColName+')'


        Sp_execute exec (@Query)

您想要复制具有不同模式名称的表结构吗？

然后看到这个..

http://dba.stackexchange.com/questions/53085/is-there-a-way-to-generate-table-create-script-in-tsql [ ^ ]

you want to make copy of a table structure with different schema name??
then see this..
http://dba.stackexchange.com/questions/53085/is-there-a-way-to-generate-table-create-script-in-tsql[^]

我认为你可以使用下面的代码snipet：

I think you can use the below code snipet:

string myConnectionString= @"DataSource =...";

SqlConnection dbConnection = new SqlConnection(myConnectionString);

string myCommand = "CREATE TABLE myTable (column1 VARCHAR(10),colunm2 INT)";

SqlCommand dbConnection = new SqlCommand(myCommand,dbConnection);

try
{
dbConnection.Open();

dbCommand.ExecuteNonQuery();
}

catch{}

dbConnection.Close();

每次都可以通过从defination表中获取数据来动态构建命令字符串。

And the command string you can build dynamically every time by fetching the data from defination table.

这篇关于通过提供表名自动创建表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！