主页 / dba / 问题 / 19938
Accepted
AmmarR
Asked: 2012-06-27 03:58:59 +0800 CST

在数据库的所有表中插入虚拟数据的脚本

  • 772

我想要一个脚本，它可以遍历数据库中的所有表及其列，并根据列类型和大小插入虚拟数据，这样我就可以开始使用数据库进行测试，并能够运行查询和测试应用程序。

sql-server t-sql

2 个回答

  1. Best Answer

    在很多情况下，@AmmarR 的解决方案无法处理 - ROWVERSION/TIMESTAMP、计算列、具有检查约束的列、外键、UNIQUEIDENTIFIER默认为 的列、默认为NEWSEQUENTIALID()的日期/时间列GETDATE()、稀疏列等。

    为什么要重新发明轮子？有一些工具可以为您生成数据，他们可能已经考虑过所有这些限制。例如DTM 数据生成器（不是免费的，没有测试过）和Red-Gate SQL 数据生成器（不是免费的，已经测试过并推荐它 - 也在这里查看）。

    • 9

  2. 我确信有不同的更好的方法来完成这项任务，例如使用游标或 sql 函数等，我想分享我完成任务的方式。

    开始创建一个存储过程，该存储过程将生成并执行插入虚拟数据的插入语句存储过程有1个参数，参数是no。要添加到所有表的行数

    脚本的逻辑很简单，选择sys.columns加入sys.types&sys.tables

    sys.columns具有所有列名具有列的数据sys.types类型sys.tables具有表名，还有其他方法可以获取表名，object_name(c.object_id) 但我认为加入 sys.columnssys.tables更方便

    下面是代码

    Create PROCEDURE [dbo].[GenerateandExecuteInsertStatmentsforTables] 
    @NoOfRows int
AS
BEGIN

Declare @icount int
declare @qry varchar(max)
set @qry = ' '
set @icount = 0
while @icount < @NoOfRows
begin
    select @qry = @qry
    +
    case when c.column_id = 1 then
        'insert into ' + SCHEMA_NAME(t.schema_id) + '.[' + t.name + '] values('
    else
        ''
    end
    +
    -- incase the column is identity, i dont include it in the insert
    case when c.is_identity = 0 then
        case when ty.name in ('bit','bigint','int','smallint','tinyint','float','decimal','numeric','money','smallmoney','real') then
            substring(CAST(  round(RAND() * 1000,0) AS varchar),1,c.max_length)
        when ty.name in ('binary','varbinary') then
            substring('0x546869732069732044756D6D792044617461',1,c.max_length)
        when ty.name In ('varchar','char','text') then
            '''' + substring('Dummy This is Dummy Data',1,c.max_length)  + '''' 
        when ty.name In ('nchar','nvarchar','ntext') then
            '''' + substring('Dummy This is Dummy Data',1,c.max_length / 2)  + ''''             
        when ty.name in('date','time','datetime','datetime2','smalldatetime','datetimeoffset') then
            '''' + convert(varchar(50),dateadd(D,Round(RAND() * 1000,1),getdate()),121) + '''' 
        when ty.name in ('uniqueidentifier') then
            cast(NEWID() as varchar(33))
        else
            ''
        end
        + 
        case when c.column_id = (Select MAX(insc.column_id) from sys.columns insc where insc.OBJECT_ID = c.OBJECT_ID) then
            ');'
        else
            ','
        end
    else
        ''  
    end

    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types AS ty ON c.user_type_id=ty.user_type_id
    ORDER BY t.name,c.column_id; 
    set @icount = @icount + 1

    --execute the insert statments
    --Select (@qry)
    exec (@qry)
    Set @qry = ' ' 
end

    之后我执行存储过程

    USE [DBNAME]
EXEC [dbo].[GenerateandExecuteInsertStatmentsforTables] 5

    这将为数据库中的所有表添加 5 行

    • 3

相关问题