该查询脚本包含以下内容(sqlserver2005):
1、查询库中所有表
2、查询库中所有表的数据(根据主键排序)
3、查询库中所有没有主键表
4、将库中所有没有主键表加上主键
5、导出库中所有外键关系重建脚本
6、删除所有外键关系
7、查询库中所有具有聚集索引的表
8、查询库中出现过的字段类型
9、查询某个字段类型在那些表中出现过
10、删除库中所有表的数据
11、查询存在计算列(包括列名和表名)
12、常用操作脚本
13、查询日志中出现过表的所有数据(根据主键排序)
2、查询库中所有表的数据(根据主键排序)
3、查询库中所有没有主键表
4、将库中所有没有主键表加上主键
5、导出库中所有外键关系重建脚本
6、删除所有外键关系
7、查询库中所有具有聚集索引的表
8、查询库中出现过的字段类型
9、查询某个字段类型在那些表中出现过
10、删除库中所有表的数据
11、查询存在计算列(包括列名和表名)
12、常用操作脚本
13、查询日志中出现过表的所有数据(根据主键排序)
常用查询(二):查询库中所有表的数据(根据主键排序)
四、查询库中所有表的数据(根据主键排序):
declare @primarykeylist varchar(200)
declare @tableName varchar(200)
declare tb_cursor cursor for
select name from sysobjects where xtype ='U'
declare @tableName varchar(200)
declare tb_cursor cursor for
select name from sysobjects where xtype ='U'
open tb_cursor
fetch next from tb_cursor into @tableName;
while @@fetch_status = 0
begin
begin
if (objectproperty(object_id(@tableName),'TableHasPrimaryKey') = 0)
begin
set @primarykeylist = null
end
else if (objectproperty(object_id(@tableName),'TableHasPrimaryKey') = 1)
begin
declare @index_id smallint
select @index_id = index_id from sys.indexes where [object_id] = object_id(@tableName) and is_primary_key = 1
declare @i int;
set @i = 1;
set @primarykeylist = ''
while (index_col(@tablename,@index_id,@i) is not null)
begin
set @primarykeylist = @primarykeylist + index_col(@tablename,@index_id,@i)+','
set @i = @i+1
end
set @primarykeylist = substring(@primarykeylist,1,len(@primarykeylist)-1)
end
end
print 'print ''' + @tableName + ''''
if @primarykeylist is not null
begin
print 'select * from '+ @tableName + ' order by ' + @primarykeylist
end
else
begin
print 'select * from '+ @tableName
end
fetch next from tb_cursor into @tableName;
end
close tb_cursor
deallocate tb_cursor
fetch next from tb_cursor into @tableName;
while @@fetch_status = 0
begin
begin
if (objectproperty(object_id(@tableName),'TableHasPrimaryKey') = 0)
begin
set @primarykeylist = null
end
else if (objectproperty(object_id(@tableName),'TableHasPrimaryKey') = 1)
begin
declare @index_id smallint
select @index_id = index_id from sys.indexes where [object_id] = object_id(@tableName) and is_primary_key = 1
declare @i int;
set @i = 1;
set @primarykeylist = ''
while (index_col(@tablename,@index_id,@i) is not null)
begin
set @primarykeylist = @primarykeylist + index_col(@tablename,@index_id,@i)+','
set @i = @i+1
end
set @primarykeylist = substring(@primarykeylist,1,len(@primarykeylist)-1)
end
end
print 'print ''' + @tableName + ''''
if @primarykeylist is not null
begin
print 'select * from '+ @tableName + ' order by ' + @primarykeylist
end
else
begin
print 'select * from '+ @tableName
end
fetch next from tb_cursor into @tableName;
end
close tb_cursor
deallocate tb_cursor
(该脚本对你有任何帮助,或需要其他功能脚本,请直接回复)
转载于:https://blog.51cto.com/dreamsky/103903