Plattenplatz MS SQL Server

USE abcdb
 
DECLARE @TABLE 
  TABLE(Id INT IDENTITY(1,1), Name VARCHAR(256))
 
INSERT INTO @TABLE
  SELECT b.name + '.'+ a.name
  FROM sys.tables a INNER JOIN sys.schemas b
  ON a.schema_id = b.schema_id
 
INSERT INTO @TABLE
SELECT '-1'
 
DECLARE @RESULT TABLE(TableName VARCHAR(256)
, TotalRows INT
, Reserved VARCHAR(50)
, DataSize VARCHAR(50)
, IndexSize VARCHAR(50)
, UnusedSize VARCHAR(50))
 
DECLARE @temp VARCHAR(256)
DECLARE @INDEX INT
SET @INDEX = 1
 
WHILE 1=1
BEGIN
  SELECT @temp = Name FROM @TABLE
  WHERE Id = @INDEX
 
  IF @temp = '-1' BREAK	
 
  INSERT @RESULT(TableName
  , TotalRows
  , Reserved
  , DataSize
  , IndexSize
  , UnusedSize)
  EXEC sp_spaceused @temp
 
  SET @INDEX = @INDEX + 1
END
 
SELECT c.name+'.'+b.name AS [TABLE], a.*
FROM @RESULT a
INNER JOIN sys.tables b ON a.TableName = b.name
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
ORDER BY TotalRows DESC

Prozesse vom MSSQL beenden

Das Skript beendet alle Prozesse einer Anwendung für einen bestimmten Benutzer. Dazu wird die Systemtabelle sysprocesses abgefragt und für jede gefundene PID der Befehl kill ausgeführt.

[code lang=“sql“]DECLARE @spid int
DECLARE CUR CURSOR FOR
SELECT spid FROM sysprocesses
WHERE program_name = ‚Name der Anwendung‘
AND loginame = ‚Name des SQL Benutzers‘
OPEN CUR
FETCH NEXT FROM CUR INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (‚KILL ‚ + @spid)
FETCH NEXT FROM CUR INTO @spid
END
CLOSE CUR
DEALLOCATE CUR[/code]