表,存储过程,视图可以通过企业管理器的脚本生成功能实现。转帖:自动生成插入语句:
create proc spGenInsertSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = 'orders'
--set @TableName = 'eeducation'
DECLARE xCursor CURSOR FOR
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
declare @F1 varchar(100)
declare @F2 integer
declare @SQL varchar(8000)
set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
OPEN xCursor
FETCH xCursor into @F1,@F2
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =@sql +
+ case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
+ 'replace(ISNULL(cast(' + @F1 + ' as varchar),''NULL''),'''''''','''''''''''')'
+ case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
+ char(13) + ''','''
FETCH NEXT FROM xCursor into @F1,@F2
END
CLOSE xCursor
DEALLOCATE xCursor
set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName
print @sql
exec (@sql)spGenInsertSQL a1
create proc spGenInsertSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = 'orders'
--set @TableName = 'eeducation'
DECLARE xCursor CURSOR FOR
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
declare @F1 varchar(100)
declare @F2 integer
declare @SQL varchar(8000)
set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
OPEN xCursor
FETCH xCursor into @F1,@F2
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =@sql +
+ case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
+ 'replace(ISNULL(cast(' + @F1 + ' as varchar),''NULL''),'''''''','''''''''''')'
+ case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + ' else '+' end
+ char(13) + ''','''
FETCH NEXT FROM xCursor into @F1,@F2
END
CLOSE xCursor
DEALLOCATE xCursor
set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName
print @sql
exec (@sql)spGenInsertSQL a1
go
exec sp_MStablerefs N'[dbo].[authors]', N'actualkeys', N'foreign'
go
exec sp_MSdependencies N'[dbo].[authors]', null, 262420
go
exec sp_MShelpcolumns N'[dbo].[authors]', 512, @orderby = 'id'
go
SELECT s.groupname FROM sysfilegroups s, sysindexes i WHERE i.groupid = s.groupid AND i.id = object_id(N'[dbo].[authors]') AND i.indid in (0, 1)
go
SELECT s.groupname FROM sysfilegroups s, sysindexes i WHERE i.id = (select x.id from sysindexes x where x.indid in (0,1) AND x.id = object_id(N'[dbo].[authors]')) AND i.groupid = s.groupid AND i.indid = 255
go
exec sp_dbcmptlevel N'pubs'
go
select c.name, null, r.name, user_name(r.uid), d.name, user_name(d.uid), null from sysobjects d right outer join ( sysobjects r right outer join syscolumns c on c.domain = r.id ) on c.cdefault = d.id and d.category & 0x0800 = 0 where c.id = object_id(N'[dbo].[authors]') and COLUMNPROPERTY(c.id, c.name, N'isIdentity') <> 1 order by c.name
go