其实很简单,相对于SQL 2000改了sys.all_objects和sys.columns
declare @tblname varchar(200)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
declare @object_id int
declare tbl_cursor CURSOR for
select name,object_id from sys.all_objects where type='u'
open tbl_cursor
FETCH NEXT FROM tbl_cursor
INTO @tblname,@object_id
while @@FETCH_STATUS = 0
Begin
declare @str varchar(8000)
declare @selectstr varchar(2000)
set @selectstr=''
set @str='INSERT INTO '+ @tblname +'('
--print @tblname,@object_id
declare @colname varchar(200)
declare col_cursor CURSOR for
select name from sys.columns where object_id=@object_id order by column_id
open col_cursor
FETCH NEXT FROM col_cursor INTO @colname
WHILE @@FETCH_STATUS = 0
BEGIN
set @str=@str+@colname+','
--Print @str
set @selectstr=@selectstr+@colname+','
--Print @colname
FETCH NEXT FROM col_cursor INTO @colname
END
close col_cursor
deallocate col_cursor
set @str=substring(@str,0,len(@str))
set @selectstr=substring(@selectstr,0,len(@selectstr))
set @str=@str+') SELECT ' + @selectstr +' FROM '+ @tblname
Print @str
fetch next from tbl_cursor into @tblname,@object_id
End
close tbl_cursor
deallocate tbl_cursor
分享到:
相关推荐
MS Sql Server 2005 Sample DataBase,这个样例数据库下载的真不容易,特别贡献出来.
SQL Server 2000 Sample Databases Northwind和pubs示例数据库SQL脚本
SQL Server 2000 Sample是一个northwind的实例数据库,你可以用它来练习SQL
SQL sample code An short introduction about SQL
SQLAlchemy示例sql炼金术的示例代码
sqlserver2008-reportservices-sample
d_sample.sql
Pubs and Northwind Database in SQL Server.
pentaho 社区版的数据库在换到mysql以后,缺少sample data数据库的创建语句,这个sql就是用来做这件事的。
SAMPLE176.sql
SAMPLE100.sql
SAMPLE149.sql
sqlserver2000的两个示例数据库源码。
SQL Server 2000 Sample Databases.rar,这个不用讲了是微软数据库带的两例子数据库,特意放上来,给有需要的人