1、通用表循环,常用于对一些分表的操作,比如这里有很多张表,表名类似delivery_0、delivery_1...的,就可以用这个来循环所有表来进行操作。原理是利用游标
declare @MyTableName varchar(255); declare My_Cursor cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'delivery\_%' escape '\'; open My_Cursor; fetch next from My_Cursor into @MyTableName; while (@@FETCH_STATUS = 0) begin exec('select * from ' + @MyTableName) fetch next from My_Cursor into @MyTableName; end close My_Cursor; deallocate My_Cursor通用表循环
ps 还可以在循环中insert 数据到临时表,然后就可以聚集所有表的数据
2、通用表数据循环,常用于遍历表内数据。这个的应用场景比较多,譬如行转列、根据A表数据,插入B表及其子表数据(可以使用这个拿到B表当前插入最新记录的主键值,然后在插入子表数据时可以使用 set @XXId = ident_current('tableName');)
Declare @row int = 1, --行记录数 @count int,--总记录数 @XXid bigint; --XXId IF EXISTS(select 1 from tempdb..sysobjects where id=object_id('tempdb..#Temp')) BEGIN DROP TABLE #Temp END SELECT ROW_NUMBER() OVER (ORDER BY t.Id ASC) rowid, tableName.* into #Temp from tableName set @count = (select COUNT(1) from #Temp) while @row <= @count BEGIN select @XXid = XXid from #Temp where rowid=@row; set @row = @row +1; END通用表数据循环
3、获取某表数据字典,可以搭配上面的【通用表循环】使用,所谓的数据字典就是打印出这张表的所有字段的简要信息
select col.name as '字段名', t.name as '类型', case when col.max_length = -1 then 'Max' else Cast(col.max_length as varchar) end as '长度', ISNULL(dv.[Default Value],'') as '默认值', case when col.is_nullable = 1 then '√' else '' end as '允许为空' from sys.columns col join sys.types t on col.system_type_id = t.system_type_id join (select SC.NAME AS "Column Name", SM.TEXT AS "Default Value", SO.name as 'Table Name', SC.colid as 'Col Id' from dbo.sysobjects SO JOIN dbo.syscolumns SC ON SO.id = SC.id LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id) dv on col.name=dv.[Column Name] and col.object_id = OBJECT_ID(dv.[Table Name]) where t.name <> 'sysname' and col.object_id = OBJECT_ID(N'tableName') order by dv.[Col Id]数据字典
4、生成指定长度的随机字符串函数(本想随机插入一些用户名,但残念的是没有汉字)
if object_id('func_random','fn') is not null drop function func_random; go create function dbo.func_random(@length int) returns nvarchar(200) as begin declare @result nvarchar(200); declare @i int, @random int; set @result = ''; set @i = 0 ; while @i < @length begin select @random = ceiling(random*150) from v_random ; --调整此值产生的范围为0~150,可能概率不同了,随机程度不一样 if (@random between 48 and 57 ) OR (@random between 65 and 90) OR (@random between 97 and 122) --0~9 A~Z a~z begin SET @result = @result + nchar(@random) set @i= @i + 1 end end; return (@result); end go /*测试随机字符串函数*/ select dbo.func_random(10);产生指定长随机字符串函数