Subin Benny's questions

我创建了一个 sp 来将我存储在表中的脚本导出到文本文件。这个 sp 做的是，如果我给 100 作为计数，那么每个文件将有 100 个来自表的脚本。我将文件名保存为 objects_1、objects_2 等等。但我需要的是。如果我有 100 个文件。那么第一个文件应该命名为 objects_001 ，objects_002 ，最后一个文件应该是 objects_100 。有人可以提出一个想法来做到这一点。,并且请确认这个sp逻辑是正确的。谢谢。

alter PROCEDURE USP_GenerateFile_Packets_new(@count int)
AS 
BEGIN 


DECLARE @ExportQry NVARCHAR(500)
DECLARE @sql NVARCHAR(500)
DECLARE @ExportPath NVARCHAR(100)
SET @ExportPath='C:\Upgrade\Scripts\'
DECLARE @BCPFileName NVARCHAR(50)
DECLARE @ReturnValue NVARCHAR(50)
DECLARE @K INT=1

declare @minval int=(select min(id) from vsl.ScriptTable)
--DECLARE @I INT=(SELECT COUNT(*) from [UpgradeDB].vsl.ScriptTable)
declare @count1 int=(select @count-1)
 WHILE @minval <=  (SELECT max(id) from [UpgradeDB].vsl.ScriptTable)
 
    BEGIN
        SET @ExportQry = 'SELECT TOP '+convert(nvarchar(100),@count)+' script FROM [UpgradeDB].vsl.ScriptTable where id>='+convert(nvarchar(100),@minval)+' and id<='+convert(nvarchar(100),@minval)+'+'+convert(nvarchar(100),@count1)+' order by id' 

        SET @sql = 'bcp "' + @ExportQry + ' " queryout ' + @ExportPath +  'OBJECTS_'+convert(nvarchar(100),@k)+'.txt -c -t^| -T -S ' + @@SERVERNAME;

    --select @sql
        EXEC @ReturnValue =  master..xp_cmdshell @sql


   SET @minval=@minval+@COUNT
   set @k=@k+1   
   END

END

--USP_GenerateFile_Packets_new 1500

如何从当前启用的另一个数据库禁用数据库中的所有触发器。然后只启用我禁用的触发器。

嗨，是否有任何查询可以获取所有自引用外键。我需要通过雪橇引用获取数据库中的所有表和外键并删除并创建它。知道该怎么做吗？

我有一个 sp 可以从孙子表中删除数据。

但我需要更改光标并使用临时表或 while 循环而不是光标。我试过但没有工作，有人可以帮忙。

Sp在下面添加

CREATE Procedure spDeleteRows
/* 
Recursive row delete procedure. 

It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on.  This is designed to do the 
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
table to find any child tables, then deletes the soon-to-be orphan records from them using 
recursive calls to this procedure. Once all child records are gone, the rows are deleted 
from the selected table.   It is designed at this time to be run at the command line. It could 
also be used in code, but the printed output will not be available.
*/
    (
    @cTableName varchar(50), / name of the table where rows are to be deleted /
    @cCriteria nvarchar(1000), / criteria used to delete the rows required /
    @iRowsAffected int OUTPUT / number of records affected by the delete /
    )
As
set nocount on
declare     @cTab varchar(255), / name of the child table /
    @cCol varchar(255), / name of the linking field on the child table /
    @cRefTab varchar(255), / name of the parent table /
    @cRefCol varchar(255), / name of the linking field in the parent table /
    @cFKName varchar(255), / name of the foreign key /
    @cSQL nvarchar(1000), / query string passed to the sp_ExecuteSQL procedure /
    @cChildCriteria nvarchar(1000), /* criteria to be used to delete 
                                           records from the child table */
    @iChildRows int / number of rows deleted from the child table /

/ declare the cursor containing the foreign key constraint information /
DECLARE cFKey CURSOR LOCAL FOR 
SELECT SO1.name AS Tab, 
       SC1.name AS Col, 
       SO2.name AS RefTab, 
       SC2.name AS RefCol, 
       FO.name AS FKName
FROM dbo.sysforeignkeys FK  
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
                              AND FK.fkey = SC1.colid 
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
                              AND FK.rkey = SC2.colid 
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
     BEGIN
    /* build the criteria to delete rows from the child table. As it uses the 
           criteria passed to this procedure, it gets progressively larger with 
           recursive calls */
    SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 
                              @cRefTab +'] WHERE ' + @cCriteria + ')'
    print 'Deleting records from table ' + @cTab
    / call this procedure to delete the child rows /
    EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
    FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
     END
Close cFKey
DeAllocate cFKey
/ finally delete the rows from this table and display the rows affected  /
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName