代码之家 › 专栏 › 技术社区 › efysis

SQL执行select返回

sql-server sql

efysis · 技术社区 · 8 年前

我有选择

select 'alter table '+so.name+
' drop '+sdc.name+' go sp_bindefault ''abc'' ,'''+so.name+'.'+sc.name+''''
from sys.objects as so
       join sys.columns as sc on so.object_id=sc.object_id
       join sys.default_constraints as sdc on sc.object_id=sdc.parent_object_id
                                                      and sc.column_id=sdc.parent_column_id
where so.type='U'
        and so.name like 'z%'
        and sdc.definition='(getdate())'

我正在删除getdate默认值,并希望绑定一个getdate默认值。结果有两行

alter table zamestnanci drop DF__zamestnan__datum__2E1BDC42 go sp_bindefault 'abc' ,'zamestnanci.datum_pridania'
alter table zamestnanci2 drop DF__zamestnan__datum__2F10007B go sp_bindefault 'abc' ,'zamestnanci2.datum_pridania'

4 回复 | 直到 6 年前

SQLBadPanda 8 年前

您将无法动态生成SQL 在单个基于集合的查询中执行它。您必须在游标或WHILE循环中迭代select的结果,并将生成的语句构建到变量中,然后使用EXEC或sp_executesql执行该语句;例如

    DECLARE @TableName sysname,
        @ConstraintName sysname,
        @ColumnName sysname,
        @sql nvarchar(4000);

DECLARE getdateDefaults CURSOR FAST_FORWARD AS
        SELECT so.name AS TableName,
               sdc.name AS ConstraintName,
               sc.name AS ColumnName
        from sys.objects as so
               join sys.columns as sc on so.object_id=sc.object_id
               join sys.default_constraints as sdc on sc.object_id=sdc.parent_object_id
                                                              and sc.column_id=sdc.parent_column_id
        where so.type='U'
                and so.name like 'z%'
                and sdc.definition='(getdate())';

OPEN getdateDefaults;
FETCH NEXT FROM getdateDefaults INTO @TableName, @ConstraintName, @ColumnName;
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @sql = N'alter table '+@TableName+N' drop '+@ConstraintName+N';'
    EXEC sys.sp_executesql  @stmt = @sql;
    SET @sql = N' sp_bindefault ''abc'' ,'''+@TableName+'.'+@ColumnName+N''';'
    EXEC sys.sp_executesql  @stmt = @sql;

    FETCH NEXT FROM getdateDefaults INTO @TableName, @ConstraintName, @ColumnName;
END
CLOSE getdateDefaults;
DEALLOCATE getdateDefaults;

HoneyBadger 8 年前

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = (your_query)

EXEC sys.sp_executesql @SQL

'alter table ' N'alter table '

Denis Luiz 8 年前

您可以将字符串放入游标中,执行这两个结果

DECLARE RS CURSOR FOR
    SELECT TOP 5 'SELECT * FROM [' + NAME + ']'
    FROM sys.Objects
    WHERE TYPE = 'U'

DECLARE @TEXT VARCHAR(MAX)

OPEN RS
FETCH NEXT FROM RS INTO @TEXT

WHILE @@FETCH_STATUS = 0 BEGIN

    FETCH NEXT FROM RS INTO @TEXT

    EXEC (@TEXT)

END

CLOSE RS
DEALLOCATE RS

SE1986 8 年前

DECLARE @tableName      NVARCHAR(255)
DECLARE @constraintName NVARCHAR(255)
DECLARE @columnName     NVARCHAR(255)
DECLARE @sql            NVARCHAR(255)

SELECT  @tableName=so.name,
        @constraintName=sdc.name,
        @columnName=sc.name
FROM    sys.objects as so
            JOIN sys.columns as sc
                ON so.object_id=sc.object_id
            JOIN sys.default_constraints as sdc
                ON sc.object_id=sdc.parent_object_id AND sc.column_id=sdc.parent_column_id
WHERE   so.type='U'AND
        so.name LIKE 'z%' AND
        sdc.definition=getdate()

SET @sql = N'ALTER TABLE ' + @tableName + ' DROP ' + @constraintName + 'go sp_bindefault ''abc'' , ''' +  @columnName + ''
EXEC sp_executesql @sql