SQL server获取建表sql语句并创建结构相同的数据库表

Zzu_zzx

已于 2022-09-15 20:43:18 修改

阅读量1.6k

点赞数 1

分类专栏：数据库 C# 文章标签： sql 数据库 c#

于 2022-09-15 20:38:44 首次发布

本文链接：https://blog.csdn.net/Zzu_zzx/article/details/126879235

版权


        /// <summary>
        /// 创建一个函数，获取建表语句
        /// </summary>
        /// <param name="connStr"></param>
        private void createFunction(string connStr)
        {
            string sqlFunc = "";
            sqlFunc += "  CREATE PROCEDURE[dbo].[sp_getCreateTableSql_zzx] (@table VARCHAR(100) ) AS BEGIN DECLARE @sql TABLE(s VARCHAR(1000), id INT IDENTITY);  ";
            sqlFunc += "   INSERT INTO @sql(s)  VALUES('create table [' + @table + '] (');  ";
            sqlFunc += "   SELECT A.name AS table_name ,B.name AS column_name ,C.value AS column_description INTO #columnsproperties FROM    sys.tables A  ";
            sqlFunc += "  INNER JOIN sys.columns B ON B.object_id = A.object_id LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id  ";
            sqlFunc += "  AND C.minor_id = B.column_id WHERE A.name = @table;  ";
            sqlFunc += "    INSERT INTO @sql(s) SELECT  '  [' + a.COLUMN_NAME + '] ' + DATA_TYPE + COALESCE('(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  ";
            sqlFunc += "    + ')', '') + ' ' + CASE WHEN EXISTS(