如何通过sql server中的查询生成sql表脚本 [英] how to generate sql table scripts through query in sql server

问题描述

大家好，



如何通过sql中的查询生成sql表脚本（包括主键，外键，索引，触发器等等）服务器。



谢谢。

Hi to all,

how to generate sql table scripts (include primary key, foreign key, indexes,triggers...so on) through query in sql server.

thanks.

推荐答案

试试这个：

Try this :

select object_definition(object_id)
from sys.objects
where type_desc in ('SQL_SCALAR_FUNCTION',
'SQL_STORED_PROCEDURE',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_TRIGGER',
'VIEW')

摘自这里 [ ^ ]





还可以看看这里：

http://www.mindsdoor.net/dmo /dmoscriptalldatabases.html [ ^ ]



[评论后]

尝试这一个来生成表格的脚本：

Extracted from Here[^]


And also have a look at here :
http://www.mindsdoor.net/dmo/dmoscriptalldatabases.html[^]

[After you comment]
Try this one to generate script of your table :

declare @vsSQL varchar(8000)
declare @vsTableName varchar(50)
select @vsTableName = 'Customers'

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) + ')'

摘自此处 [ ^ ]



使用谷歌查找有关此问题的更多脚本。



如果您希望将表部署到客户端，那么如何使用SSMS的脚本生成器？



希望它有所帮助。

Extracted from Here[^]

Use google to find more scripts about this issue.

And if you want this to deploy your tables to client side so how about using script generator of SSMS ?

Hope it helps.

declare @table varchar(100)
set @table = 'MyTable' -- set table name here
declare @sql table(s varchar(1000), id int identity)

-- create statement
insert into  @sql(s) values ('create table [' + @table + '] (')

-- column list
insert into @sql(s)
select 
    '  ['+column_name+'] ' + 
    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    case when exists ( 
        select id from syscolumns
        where object_name(id)=@table
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
    ) then
        'IDENTITY(' + 
        cast(ident_seed(@table) as varchar) + ',' + 
        cast(ident_incr(@table) as varchar) + ')'
    else ''
    end + ' ' +
    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','

 from information_schema.columns where table_name = @table
 order by ordinal_position

-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='PRIMARY KEY'

if ( @pkname is not null ) begin
    insert into @sql(s) values('  PRIMARY KEY (')
    insert into @sql(s)
        select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage
        where constraint_name = @pkname
        order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end
else begin
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
end

-- closing bracket
insert into @sql(s) values( ')' )

-- result!
select s from @sql order by id

通过以下链接查看不同的例子..，



这里 [ ^ ]

Go through the below link for different examples..,

Here[^]

这篇关于如何通过sql server中的查询生成sql表脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！