mssql实用写法汇总

2022-08-21 10:50:54

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);

产生指定长随机字符串函数

mssql实用写法汇总

码农公寓

相关文章