sqlserver 建表语句，获取建表语句的存储过程，包括排序规则，索引，字段说明，支持同时生成多个表...

最新推荐文章于 2023-06-13 09:01:59 发布
最新推荐文章于 2023-06-13 09:01:59 发布
阅读量115 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/davidhou/p/9112222.html
版权

先创建一个分割表名的分割函数

--表值函数用以截取字符串
--如果为其添加一列主键id,则其顺序就会固定了
create FUNCTION [Split](@text NVARCHAR(max))
RETURNS @tempTable TABLE(value NVARCHAR(1000))
AS
BEGIN
     DECLARE @StartIndex INT                --开始查找的位置
     DECLARE @FindIndex INT                --找到的位置
     DECLARE @Content    VARCHAR(4000)    --找到的值
     --初始化一些变量
     SET @StartIndex = 1 --T-SQL中字符串的查找位置是从1开始的
     SET @FindIndex=0
    
     --开始循环查找字符串逗号
     WHILE(@StartIndex <= LEN(@Text))
     BEGIN
         --查找字符串函数 CHARINDEX 第一个参数是要找的字符串
         --                            第二个参数是在哪里查找这个字符串
         --                            第三个参数是开始查找的位置
         --返回值是找到字符串的位置
         SELECT @FindIndex = CHARINDEX(',' ,@Text,@StartIndex)
         --判断有没找到 没找到返回0
         IF(@FindIndex =0 OR @FindIndex IS NULL)
         BEGIN
             --如果没有找到就表示找完了
             SET @FindIndex = LEN(@Text)+1
         END
         --截取字符串函数 SUBSTRING 第一个参数是要截取的字符串
         --                            第二个参数是开始的位置
         --                            第三个参数是截取的长度
         SET @Content =SUBSTRING(@Text,@StartIndex,@FindIndex-@StartIndex)
         --初始化下次查找的位置
         SET @StartIndex = @FindIndex+1
         --把找的的值插入到要返回的Table类型中
         INSERT INTO @tempTable (Value) VALUES (@Content)
     END
     RETURN
END

创建获得建表语句的存储过程

create PROCEDURE [sp_gettext]
    @name VARCHAR(max) = NULL
,    @identity BIT = 1
,    @index TINYINT = 2 -- 0不创建索引  1不创建表  2创建索引  
,    @new BIT =0 
AS
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET NUMERIC_ROUNDABORT OFF;

DECLARE    @crlf CHAR(2);
SET @crlf = CHAR(13) + CHAR(10);
DECLARE    @objid INT;

DECLARE @results TABLE (definition NVARCHAR(max))

DECLARE    @objects TABLE (id VARCHAR(100), type CHAR(2));

WITH    db1(dbname)
            AS (SELECT    [value] AS dbname
                FROM    Split(@name)),
        db2    AS (SELECT   
              --CASE WHEN CHARINDEX('.', dbname) = 0 AND CHARINDEX('[', dbname) = 0 THEN '[cn9c080].' + QUOTENAME(dbname)
     --                            ELSE dbname
     --                       END AS dbname
                        CASE WHEN o.[object_id] IS NULL AND tt.[name] IS NULL THEN '%' ELSE LTRIM(ISNULL(o.[object_id],tt.[type_table_object_id])) END AS object_id,
                        CASE  [db1].[dbname] WHEN 'tables' THEN 'U' WHEN 'procs' THEN 'P' else
                         o.[type] END type
                FROM    db1
                left JOIN sys.[objects] o ON (PARSENAME(db1.[dbname],1) = o.[name] OR OBJECT_ID('[cn9c080].' + QUOTENAME(dbname)) = o.[object_id])
                LEFT JOIN sys.table_types tt ON db1.dbname=tt.NAME 
                )
                

    INSERT    INTO @objects
    SELECT    *
    FROM    db2    ;
    --OPTION    (MAXRECURSION 0);    

    --SELECT TOP 100 * FROM @objects;
     
    WITH    ColumnDefs
                AS (SELECT    TableObj = c.[object_id], ColSeq = c.column_id,
                            ColumnDef = QUOTENAME(c.name) + ' ' + CASE    WHEN c.is_computed = 1
                                                                        THEN 'as ' + COALESCE(k.[definition], '') + CASE
                                                                                                        WHEN k.is_persisted = 1
                                                                                                        THEN ' PERSISTED' + CASE
                                                                                                        WHEN k.is_nullable = 0
                                                                                                        THEN ' NOT NULL'
                                                                                                        ELSE ''
                                                                                                        END
                                                                                                        ELSE ''
                                                                                                        END
                                                                        ELSE DataType + CASE    WHEN DataType IN (
                                                                                                        'decimal',
                                                                                                        'numeric')
                                                                                                THEN '(' + CAST(c.precision AS VARCHAR(10)) + CASE
                                                                                                        WHEN c.scale <> 0
                                                                                                        THEN ',' + CAST(c.scale AS VARCHAR(10))
                                                                                                        ELSE ''
                                                                                                        END + ')'
                                                                                                WHEN DataType IN ('char',
                                                                                                        'varchar',
                                                                                                        'nchar',
                                                                                                        'nvarchar',
                                                                                                        'binary',
                                                                                                        'varbinary')
                                                                                                THEN '(' + CASE
                                                                                                        WHEN c.max_length = -1
                                                                                                        THEN 'max'
                                                                                                        ELSE CASE
                                                                                                        WHEN DataType IN (
                                                                                                        'nchar',
                                                                                                        'nvarchar')
                                                                                                        THEN CAST(c.max_length / 2 AS VARCHAR(10))
                                                                                                        ELSE CAST(c.max_length AS VARCHAR(10))
                                                                                                        END
                                                                                                        END + ')'
                                                                                                WHEN DataType = 'float' AND c.precision <> 53
                                                                                                THEN '(' + CAST(c.precision AS VARCHAR(10)) + ')'
                                                                                                WHEN DataType IN ('time',
                                                                                                        'datetime2',
                                                                                                        'datetimeoffset') AND c.scale <> 7
                                                                                                THEN '(' + CAST(c.scale AS VARCHAR(10)) + ')'
                                                                                                ELSE ''
                                                                                        END
                                                                    END + CASE    WHEN c.is_identity = 1 AND @identity = 1
                                                                                THEN ' IDENTITY(' + CAST(IDENT_SEED(QUOTENAME(OBJECT_SCHEMA_NAME(c.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(c.[object_id]))) AS VARCHAR(30)) + ',' + CAST(IDENT_INCR(QUOTENAME(OBJECT_SCHEMA_NAME(c.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(c.[object_id]))) AS VARCHAR(30)) + ')'
                                                                                ELSE ''
                                                                            END + CASE    WHEN c.is_rowguidcol = 1
                                                                                        THEN ' ROWGUIDCOL'
                                                                                        ELSE ''
                                                                                    END + CASE    WHEN c.xml_collection_id > 0
                                                                                                THEN ' (CONTENT ' + QUOTENAME(SCHEMA_NAME(x.schema_id)) + '.' + QUOTENAME(x.name) + ')'
                                                                                                ELSE ''
                                                                                            END + CASE    WHEN c.is_computed = 0 AND UserDefinedFlag = 0
                                                                                                        THEN CASE
                                                                                                        WHEN c.collation_name <> CAST(DATABASEPROPERTYEX(DB_NAME(),
                                                                                                        'collation') AS NVARCHAR(128))
                                                                                                        THEN ' COLLATE ' + c.collation_name
                                                                                                        ELSE ''
                                                                                                        END
                                                                                                        ELSE ''
                                                                                                    END + CASE
                                                                                                        WHEN c.is_computed = 0
                                                                                                        THEN CASE
                                                                                                        WHEN c.is_nullable = 0
                                                                                                        THEN ' NOT'
                                                                                                        ELSE ''
                                                                                                        END + ' NULL'
                                                                                                        ELSE ''
                                                                                                        END + CASE                                                                                                        
                                                                                                        WHEN c.default_object_id > 0 AND ISNULL(@new,0) = 0
                                                                                                        THEN ' CONSTRAINT ' + QUOTENAME(d.name) + ' DEFAULT ' + COALESCE(d.[definition],
                                                                                                        '')
                                                                                                        WHEN c.default_object_id > 0 AND ISNULL(@new,0) = 1
                                                                                                        THEN ' DEFAULT ' + COALESCE(d.[definition],
                                                                                                        '')
                                                                                                        ELSE ''
                                                                                                        END
                    FROM    sys.columns c
                    CROSS APPLY (SELECT    DataType = TYPE_NAME(c.user_type_id)
                                    ,    UserDefinedFlag = CASE    WHEN c.system_type_id = c.user_type_id THEN 0
                                                                ELSE 1
                                                            END) F1
                    LEFT JOIN sys.default_constraints d ON c.default_object_id = d.[object_id]
                    LEFT JOIN sys.computed_columns k ON c.[object_id] = k.[object_id] AND c.column_id = k.column_id
                    LEFT JOIN sys.xml_schema_collections x ON c.xml_collection_id = x.xml_collection_id),
            IndexDefs
                AS (SELECT    TableObj = i.[object_id], IxName = QUOTENAME(i.name+CASE WHEN @new=1 THEN '_'+LEFT(NEWID(),4) ELSE '' end), IxPKFlag = i.is_primary_key,
                            IxType = CASE    WHEN i.is_primary_key = 1 THEN 'PRIMARY KEY '
                                            WHEN i.is_unique = 1 THEN 'UNIQUE '
                                            ELSE ''
                                        END + LOWER(type_desc),
                            IxDef = '(' + IxColList + ')' + COALESCE(' INCLUDE (' + IxInclList + ')', ''),
                            IxOpts = IxOptList
                    FROM    sys.indexes i
                    LEFT JOIN sys.stats s ON i.index_id = s.stats_id AND i.[object_id] = s.[object_id]
                    CROSS APPLY (SELECT    STUFF((SELECT    CASE    WHEN i.is_padded = 1 THEN ', PAD_INDEX=ON'
                                                                ELSE ''
                                                        END + CASE    WHEN i.fill_factor <> 0
                                                                    THEN ', FILLFACTOR=' + CAST(i.fill_factor AS VARCHAR(10))
                                                                    ELSE ''
                                                                END + CASE    WHEN i.ignore_dup_key = 1
                                                                            THEN ', IGNORE_DUP_KEY=ON'
                                                                            ELSE ''
                                                                        END + CASE    WHEN s.no_recompute = 1
                                                                                    THEN ', STATISTICS_RECOMPUTE=ON'
                                                                                    ELSE ''
                                                                                END + CASE    WHEN i.allow_row_locks = 0
                                                                                            THEN ', ALLOW_ROW_LOCKS=OFF'
                                                                                            ELSE ''
                                                                                        END + CASE    WHEN i.allow_page_locks = 0
                                                                                                    THEN ', ALLOW_PAGE_LOCKS=OFF'
                                                                                                    ELSE ''
                                                                                                END), 1, 2, '')) F_IxOpts (IxOptList)
                    CROSS APPLY (SELECT    STUFF((SELECT    ',' + QUOTENAME(c.name) + CASE    WHEN ic.is_descending_key = 1 AND i.type <> 3
                                                                                        THEN ' DESC'
                                                                                        WHEN ic.is_descending_key = 0 AND i.type <> 3
                                                                                        THEN ' ASC'
                                                                                        ELSE ''
                                                                                    END
                                                FROM    sys.index_columns ic
                                                JOIN    sys.columns c ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
                                                WHERE    ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.is_included_column = 0
                                                ORDER BY ic.key_ordinal    
                                        FOR        XML    PATH('')
                                                ,    TYPE).value('.', 'nvarchar(max)'), 1, 1, '')) F_IxCols (IxColList)
                    CROSS APPLY (SELECT    STUFF((SELECT    ',' + QUOTENAME(c.name)
                                                FROM    sys.index_columns ic
                                                JOIN    sys.columns c ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
                                                WHERE    ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.is_included_column = 1
                                                ORDER BY ic.key_ordinal    
                                        FOR        XML    PATH('')
                                                ,    TYPE).value('.', 'nvarchar(max)'), 1, 1, '')) F_IxIncl (IxInclList)
                    WHERE    i.type_desc <> 'HEAP'),
            FKDefs
                AS (SELECT    TableObj = f.parent_object_id, FKName = QUOTENAME(f.name),
                            FKRef = QUOTENAME(OBJECT_SCHEMA_NAME(f.referenced_object_id)) + '.' + QUOTENAME(OBJECT_NAME(f.referenced_object_id)),
                            FKColList = ParentColList, FKRefList = RefColList,
                            FKDelOpt = CASE f.delete_referential_action
                                            WHEN 1 THEN 'CASCADE'
                                            WHEN 2 THEN 'SET NULL'
                                            WHEN 3 THEN 'SET DEFAULT'
                                        END, FKUpdOpt = CASE f.update_referential_action
                                                            WHEN 1 THEN 'CASCADE'
                                                            WHEN 2 THEN 'SET NULL'
                                                            WHEN 3 THEN 'SET DEFAULT'
                                                        END, FKNoRepl = f.is_not_for_replication
                    FROM    sys.foreign_keys f
                    CROSS APPLY (SELECT    STUFF((SELECT    ',' + QUOTENAME(c.name)
                                                FROM    sys.foreign_key_columns k
                                                JOIN    sys.columns c ON k.parent_object_id = c.[object_id] AND k.parent_column_id = c.column_id
                                                WHERE    k.constraint_object_id = f.[object_id]
                                                ORDER BY constraint_column_id
                                        FOR        XML    PATH('')
                                                ,    TYPE).value('.', 'nvarchar(max)'), 1, 1, '')) F_Parent (ParentColList)
                    CROSS APPLY (SELECT    STUFF((SELECT    ',' + QUOTENAME(c.name)
                                                FROM    sys.foreign_key_columns k
                                                JOIN    sys.columns c ON k.referenced_object_id = c.[object_id] AND k.referenced_column_id = c.column_id
                                                WHERE    k.constraint_object_id = f.[object_id]
                                                ORDER BY constraint_column_id
                                        FOR        XML    PATH('')
                                                ,    TYPE).value('.', 'nvarchar(max)'), 1, 1, '')) F_Ref (RefColList))
        INSERT INTO @results ([definition])
        SELECT  --TableName, 
                [definition]+CHAR(10)+'GO'+CHAR(10) AS definition            
        FROM    sys.tables t
        INNER JOIN @objects o ON(t.[object_id] LIKE o.[id] AND o.[type]='U')
        CROSS APPLY (SELECT    TableName = QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id])) + '.' + QUOTENAME(OBJECT_NAME(t.[object_id]))) F_Name
        CROSS APPLY (SELECT    STUFF((SELECT    @crlf + '  ,' + ColumnDef
                                    FROM    ColumnDefs
                                    WHERE    TableObj = t.[object_id]
                                    ORDER BY ColSeq    
                            FOR        XML    PATH('')
                                    ,    TYPE).value('.', 'nvarchar(max)'), 1, 5, '')) F_Cols (ColumnList)
        CROSS APPLY (SELECT    STUFF((SELECT    @crlf + '  ,CONSTRAINT ' + QUOTENAME(name) + ' CHECK ' + CASE
                                                                                                        WHEN is_not_for_replication = 1
                                                                                                        THEN 'NOT FOR REPLICATION '
                                                                                                        ELSE ''
                                                                                                        END + COALESCE([definition],
                                                                                                        '')
                                    FROM    sys.check_constraints
                                    WHERE    parent_object_id = t.[object_id]
                            FOR        XML    PATH('')
                                    ,    TYPE).value('.', 'nvarchar(max)'), 1, 2, '')) F_Const (ChkConstList)
        CROSS APPLY (SELECT    STUFF((SELECT    @crlf + '  ,CONSTRAINT ' + IxName + ' ' + IxType + ' ' + IxDef + COALESCE(' WITH (' + IxOpts + ')',
                                                                                                        '')
                                    FROM    IndexDefs
                                    WHERE    TableObj = t.[object_id] AND IxPKFlag = 1
                            FOR        XML    PATH('')
                                    ,    TYPE).value('.', 'nvarchar(max)'), 1, 2, '')) F_IxConst (IxConstList)
        CROSS APPLY (SELECT    STUFF((SELECT    @crlf + '  ,CONSTRAINT ' + FKName + ' FOREIGN KEY ' + '(' + FKColList + ')' + ' REFERENCES ' + FKRef + ' (' + FKRefList + ')' + CASE
                                                                                                        WHEN FKDelOpt IS NOT NULL
                                                                                                        THEN ' ON DELETE ' + FKDelOpt
                                                                                                        ELSE ''
                                                                                                        END + CASE
                                                                                                        WHEN FKUpdOpt IS NOT NULL
                                                                                                        THEN ' ON UPDATE ' + FKUpdOpt
                                                                                                        ELSE ''
                                                                                                        END + CASE
                                                                                                        WHEN FKNoRepl = 1
                                                                                                        THEN ' NOT FOR REPLICATION'
                                                                                                        ELSE ''
                                                                                                        END
                                    FROM    FKDefs
                                    WHERE    TableObj = t.[object_id]
                            FOR        XML    PATH('')
                                    ,    TYPE).value('.', 'nvarchar(max)'), 1, 2, '')) F_Keys (FKConstList)
        CROSS APPLY (SELECT    STUFF((SELECT    @crlf + 'CREATE ' + IxType + ' INDEX ' + IxName + ' ON ' + TableName + ' ' + IxDef + COALESCE(' WITH (' + IxOpts + ')',
                                                                                                        '')
                                    FROM    IndexDefs
                                    WHERE    TableObj = t.[object_id] AND IxPKFlag = 0
                            FOR        XML    PATH('')
                                    ,    TYPE).value('.', 'nvarchar(max)'), 1, 2, '')) F_Indexes (IndexList)
        CROSS APPLY (SELECT    [definition] =
            ( SELECT    CASE    WHEN @index <> 1
                                THEN 'CREATE TABLE ' + TableName + @crlf + '(' + @crlf + '   ' + ColumnList + COALESCE(@crlf + ChkConstList,
                                                                                                        '') + COALESCE(@crlf + IxConstList,
                                                                                                        '') + COALESCE(@crlf + FKConstList,
                                                                                                        '') + @crlf + ')' + @crlf
                                ELSE ''
                        END + CASE    WHEN @index <> 0 THEN COALESCE(@crlf + IndexList, '')
                                    ELSE ''
                                END    
                        FOR    XML    PATH('')
                            ,    TYPE).value('.', 'nvarchar(max)')) F_Link
        WHERE    t.[is_ms_shipped] = 0 AND [definition] <> '';


        -- 视图/过程/函数/触发器
    INSERT INTO @results ([definition])
    SELECT  --QUOTENAME(object_schema_name(m.object_id))+'.'+ QUOTENAME(object_name(m.object_id)) AS [name],o.type,
            m.definition+CHAR(10)+'GO'+CHAR(10) AS definition            
    FROM    sys.sql_modules m
    INNER JOIN sys.objects o ON m.object_id = o.object_id
    INNER JOIN @objects a ON(m.[object_id] LIKE a.[id] AND a.[type] <>'U');    

    --houpeidong 2018-05-30 add 字段说明
    insert into @results ([definition])
    select 
    'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''' 
    + cast(ep.[value] as varchar(100)) 
    +''', @level0type=N''SCHEMA'',@level0name=N'''
    +schema_name(schema_id)
    +''', @level1type=N''TABLE'',@level1name=N'''
    +t.[name]
    +''', @level2type=N''COLUMN'',@level2name=N'''
    +c.[name]
    +''''
    +CHAR(10)+'GO'+CHAR(10)
    --,t.[name] AS 表名,c.[name] AS 字段名,cast(ep.[value] as varchar(100)) AS [字段说明]  ,c.collation_name,t.create_date,t.modify_date,schema_name(schema_id),*
    FROM sys.tables AS t  
    INNER JOIN sys.columns AS c ON t.object_id = c.object_id  
    LEFT JOIN sys.extended_properties AS ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id 
    INNER JOIN @objects o ON(t.[object_id] LIKE o.[id] AND o.[type]='U')
    WHERE ep.class =1 --AND t.name='tbCertifyUserApply'
        
    SELECT * FROM @results;
    -- 用户定义类型. 不过对用户定义表类型尚不完善
    --;WITH TypeDef AS(
 -- SELECT TypeName=QUOTENAME( SCHEMA_NAME(t.schema_id))+'.'+QUOTENAME(t.name)
 --       ,ParentName=TYPE_NAME(t.system_type_id)+''
 --                +case
 --                   when DataType in ('decimal','numeric') then '('+cast(t.precision as varchar(10))+case when t.scale<>0 then ','+cast(t.scale as varchar(10)) else '' end +')'
 --                   when DataType in ('char','varchar','nchar','nvarchar','binary','varbinary') then '('+case when t.max_length=-1 then 'max' else case when DataType in ('nchar','nvarchar') then cast(t.max_length/2 as varchar(10)) else cast(t.max_length as varchar(10)) end end +')'
 --                   when DataType='float' and t.precision<>53 then '('+cast(t.precision as varchar(10))+')'
 --                   when DataType in ('time','datetime2','datetimeoffset') and t.scale<>7 then '('+cast(t.scale as varchar(10))+')'
 --                   else ''
 --                 end
 --            +case when t.is_nullable=0 then ' NOT' else '' end+' NULL' 
 -- from sys.types t
 -- cross apply (  
 --   select DataType=type_name(t.system_type_id)
 --         ) F1
 -- WHERE t.is_user_defined=1
 -- )
 -- SELECT TypeName,'CREATE TYPE '+TypeName+' FROM ' +ParentName AS definition FROM TypeDef

执行示例：

--单个表
exec [sp_gettext] 'tablename'
--多个表
exec [sp_gettext] 'tablename1,tablename2,tablename3'

 

转载于:https://www.cnblogs.com/davidhou/p/9112222.html

weixin_30765505
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
sqlserver2005自动创建数据表和自动添加某个字段索引
01-19
创建数据表的SQL语句如下： string tatlename = “T_useruid”;//定义一个变量。用于自动创建数据表的名称，当前表名为：T_useruid string sql = “CREATE TABLE [dbo].[” + tatlename + “]([Cid] [int] IDENTITY...
SQLSERVER的排序问题结果不是想要的
12-15
有可能你的表里有一个字段已经建立了索引 你想结果集按照那个建立了索引的字段排序，那么你不指定“order by”是没有问题的，因为表的存储顺序就是按照那个字段的顺序排好序了，所以可以不指定“order by”，但是...
SQLSERVER 存储过程-临时表存储&游标循环
最新发布
blue_wmm的博客
06-13 1774
FOR SELECT TOP 99999999 id,tenant_id,process_seq,workstations FROM rms_process_route_detail WHERE is_deleted = 0 AND process_route_id = @process_route_id ORDER BY process_seq --查出需要的集合放到游标中。print '工艺路线标识(内部)111:'+convert(varchar,@indexMin)
存储过程——实现将查询出的数据作为列新建表
心若向阳 无谓悲伤
12-05 1749
本文章案例：将从用户表(sys_user)查出的用户名(name)作为新的列名，创建一个全新的表minfo。 了解完需求，开始编写存储过程。 第一步：打开PL/SQL可视化工具，从左下侧找到“Procedures”，右键“Procedures”文件夹，点击“New...”,出现以下弹窗 第二步：写入存储过程名字（驼峰命名规则）和所需参数（可有可无） 编写完信息后点击“OK”便会出现...
一个很简单的获得sqlserver建表sql（create table）的方法
真的一地白菜
06-15 4871
打开sqlserver企业管理器，在表列表上选择一个（或多个，只是时间有差别）表，ctrl+c，然后到记事本里，ctrl+v，稍等……，create table语句就会出现，同样的方法也适用于view，function，proc等
[332]sql快速创建一张相同表结构的表
周小董
07-14 1万+
根据t_bond_basic_info表,创建表结构相同的表，取老表中的一部分字段t_bond_basic_info_new 后面的条件 where id&amp;gt;146000 创建的时候把id&amp;gt;146000的数据写到新表中，如果where 1&amp;lt;&amp;gt;1,只创建表结构，不把数据写进去 CREATE TABLE t_bond_basic_info_new select id, ...
创建一个与已知表相同的 表结构
有 prepare , No out
12-02 2899
create table mes_ (select * from mes );
MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总结
09-09
主要介绍了MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法,总结分析了MySQL建表、编码设置、字段添加、索引操作所涉及的SQL语句,需要的朋友可以参考下
用sql命令修改数据表中的一个字段为非空(not null)的语句
01-20
ALTER TABLE table1 ALTER COLUMN ... 您可能感兴趣的文章:sql server建库、建表、建约束技巧mysql建库时提示Specified key was too long max key length is 1000 bytes的问题的解决方法Mysql 建库建表技巧分享SQL S
30个mysql千万级大数据SQL查询优化技巧详解
12-16
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select...
sqlserver 建表语句，获取建表语句的存储过程，包括排序规则，索引，字段说明，支持同时生成多个表
极客神殿
05-16 564
先创建一个分割表名的分割函数 --表值函数用以截取字符串 --如果为其添加一列主键id,则其顺序就会固定了 create FUNCTION [Split](@text NVARCHAR(max)) RETURNS @tempTable TABLE(value NVARCHAR(1000)) AS BEGIN DECLARE @StartIndex INT -...
如何数据表(SQL Server)的创建
qq_44858151的博客
03-23 303
一.数据表的创建 1.使用企业管理器创建 找一个数据库---------打开找------->表文件夹------右键--------->新建---------找--------->表-----点击----->出现表的书写格式只需要自行填写--------------->显示表的时候右键找编辑钱200/1000可以针对那个表具体字段加约束 2.使用查询分析器创建 （1...
sqlserver建立相同的表结构
weixin_34310127的博客
08-14 892
select * into purpose from source来自为知笔记(Wiz) 转载于:https://www.cnblogs.com/wuqingzangyue/p/5770034.html
SQL Server创建表语句介绍
热门推荐
kepa520的博客
12-13 23万+
原文地址：http://database.51cto.com/art/201010/231018.htm SQL Server创建表是最常见也是最常用的操作之一，下面就为您介绍SQL Server创建表的语句写法，供您参考，希望可以让您对SQL Server创建表方面有更深的认识。 USE suntest  create table 仓库  (  仓库编号 int ,   仓库
sql：mysql：完整的复制一张表(结构，索引，数据)
不花的花和尚的博客
12-07 5520
CREATE TABLE 复制表 LIKE 表; INSERT INTO 复制表 SELECT * FROM 表;     hive： insert into t_positions (title,spider_url) select  p_name as title, request_url as spider_url from  position_hbase  where p_na...
sql server 同时更新多个字段分别来源多个表
04-25
1. 使用索引：在各个表的关键字段上创建索引可以加快查询速度，从而提高性能。 2. 优化 SQL 查询：使用合适的 SQL 查询语句、优化 SQL 查询语句、减少 Join 语句的数量等方法可以提高性能。 3. 批量更新：将多个...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值