http://education.sqlfarms.com/education/ShowPost.aspx?PostID=51
------------------------------------------------------------------
This is a short script that returns all table names in the current database, together with their foreign key (FK) hierarchy level, and the table(s) that they reference (when applicable). The value of the FK hierarchy associated with each table is determined as follows: If a table does not have a FK constraint (i.e., it does not reference any other tables via a FK, or in other words - the table is not a foreign table in any FK relationship), then it is of level 0 in the hierarchy. If the table references one or more tables, which do not reference any other tables, then the current table is of level 1, and so on. The tables referenced by each FK (i.e., primary tables) are returned by the script as well, for each FK relationship found. Moreover, if a table references itself (and no other tables), then it is considered as a level 0 table.
The script is useful when one wishes to INSERT data into several tables, or DROP tables, and needs to determine the table order to follow - tables of hierarchy 0 must be inserted into first, then those of hierarchy 1, and so on. Similarly, tables with the highest hierarchy should be dropped first, and those with hierarchy 0 should be dropped last.
To return the table FK hierarchy info, the script uses the following algorithm:
First, get all DB tables that do not have any FK constraints. Then get all tables that have a FK that only reference one or more of the tables that don't have any FKs. Then, get the tables that have FKs mapped to the already collected tables, and so on. The entire algorithm is run in a simple WHILE loop.
/*==================================================================================
NAME: Get foreign key hierarchy of all DB tables
(to determine tables INSERT or DROP order, for example)
DESCRIPTION: This is a short script that returns all table names
in the current database, together with their foreign key (FK)
hierarchy level, and the table(s) that they reference (when
applicable). The value of the FK hierarchy associated with
each table is determined as follows: If a table does not
have a FK constraint (i.e., it does not reference any other
tables via a FK, or in other words - the table is not a foreign
table in any FK relationship), then it is of level 0 in the
hierarchy. If the table references one or more tables,
which do not reference any other tables, then the current
table is of level 1, and so on. The tables referenced by
each FK (i.e., primary tables) are returned by the script
as well, for each FK relationship found. Moreover, if a table
references itself (and no other tables), then it is
considered as a level 0 table.
The script is useful when one wishes to INSERT data into
several tables, or DROP tables, and needs to determine the
table order to follow - tables of hierarchy 0 must be
inserted into first, then those of hierarchy 1, and so on.
Similarly, tables with the highest hierarchy should be dropped
first, and those with hierarchy 0 should be dropped last.
To return the table FK hierarchy info, the script uses the
following algorithm: First, get all DB tables that do not
have any FK constraints. Then get all tables that have a
FK that only reference one or more of the tables that don't
have any FKs. Then, get the tables that have FKs mapped
to the already collected tables, and so on. The entire
algorithm is run in a simple WHILE loop.
USER PARAMETERS: NA
RESULTSET: TableName, HierarchyLevel, FKName, FKReference (the primary
table in the FK relationship, where applicable)
RESULTSET SORT: NA
USING TABLES/VIEWS: INFORMATION_SCHEMA.TABLES
sysreferences
REVISIONS
DATE DEVELOPER DESCRIPTION OF REVISION VERSION
========= =============== ================================= ===========
05/05/2005 Omri Bahat Initial release 1.00
==================================================================================
Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
This code may be used at no charge as long as this copyright notice is not removed.
==================================================================================*/
-- Get FK hierarchy of all DB tables
SET NOCOUNT ON
DECLARE @i INT
DECLARE @Cnt INT
-- The variable @i is the hierarchy level.
-- The variable @Cnt hold the number of tables returned in the
-- last run of the loop, which tells when the loop should exist.
SET @i = 0
SET @Cnt = 1
IF OBJECT_ID('tempdb..#tblFKTableOrder', 'U') IS NOT NULL
DROP TABLE #tblFKTableOrder
CREATE TABLE #tblFKTableOrder (
TableName NVARCHAR(128),
HierarchyLevel INT,
FKName NVARCHAR(128),
FKReference NVARCHAR(128))
-- First, grab all the tables that don't have any FK constraints, as hierarchy level 0.
INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT TABLE_NAME, @i, N'', N''
FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK)
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasForeignKey') = 0
-- Second, get all tables that only have self-referencing (and no other) FKs.
-- In the query below - RS1 contains all table names that references themselves
-- (and possible other tables), and RS2 contains all tables that reference other tables.
-- The desired tables are all those in RS1 that are not in RS2.
INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT OBJECT_NAME(RS1.fkeyid), @i, OBJECT_NAME(RS1.constid), OBJECT_NAME(RS1.rkeyid)
FROM (SELECT fkeyid, constid, rkeyid
FROM sysreferences WITH (NOLOCK)
WHERE rkeyid = fkeyid ) RS1
LEFT OUTER JOIN
(SELECT DISTINCT fkeyid
FROM sysreferences WITH (NOLOCK)
WHERE fkeyid <> rkeyid ) RS2
ON RS1.fkeyid = RS2.fkeyid
WHERE RS2.fkeyid IS NULL
-- Now, drill down in the FK hierarchy. Get all tables
-- that have a FK that references one or more tables in #tblFKTableOrder,
-- yet only references tables that are in #tblFKTableOrder(!), and that have not yet
-- been recorded in #tblFKTableOrder. Tables that reference themselves, as well
-- as tables in #tblFKTableOrder, are considered as well.
-- This is done in a loop, and the loop terminates when we reach the lowest level
-- in the hierarchy (i.e., when no more tables meet the listed condition).
WHILE @Cnt > 0
BEGIN
-- Analyze the next level in the hierarchy.
SET @i = @i + 1
-- Get all tables that reference tables that are recorded
-- in #tblFKTableOrder (can also reference themselves),
-- and do not references tables that
-- were not yet recorded.
-- This is done by as follows:
-- RS1 conatains the tables that have FK constraints
-- that reference tables in #tblFKTableOrder (and possibly have
-- a self-reference). RS2 contains all tables that reference tables
-- that are not yet in #tblFKTableOrder (excluding self-refences).
-- We write into #tblFKTableOrder the tables in RS1, which are
-- not in RS2.
INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT OBJECT_NAME(a.fkeyid), @i, OBJECT_NAME(a.constid), OBJECT_NAME(a.rkeyid)
FROM sysreferences a
INNER JOIN
(SELECT DISTINCT z.fkeyid
FROM sysreferences z WITH (NOLOCK)
INNER JOIN #tblFKTableOrder y WITH (NOLOCK)
ON OBJECT_NAME(z.rkeyid) = y.TableName
LEFT OUTER JOIN #tblFKTableOrder v WITH (NOLOCK)
ON OBJECT_NAME(z.fkeyid) = v.TableName
WHERE v.TableName IS NULL) RS1
ON a.fkeyid = RS1.fkeyid
LEFT OUTER JOIN
(SELECT DISTINCT x.fkeyid
FROM sysreferences x WITH (NOLOCK)
LEFT OUTER JOIN #tblFKTableOrder w WITH (NOLOCK)
ON OBJECT_NAME(x.rkeyid) = w.TableName
WHERE x.fkeyid <> x.rkeyid
AND w.TableName IS NULL) RS2
ON RS1.fkeyid = RS2.fkeyid
WHERE RS2.fkeyid IS NULL
SET @Cnt = @@ROWCOUNT
END
SET NOCOUNT OFF
SELECT * FROM #tblFKTableOrder
ORDER BY HierarchyLevel ASC, TableName ASC, FKName ASC
GO
maandag 29 december 2008
FindDependencies.sql
http://education.sqlfarms.com/education/ShowPost.aspx?PostID=216
-- This script should be run on a database where a searched object resides.
-- The script then looks at all the code on the current database and all other
-- databases on the server, and searches for all references to the object.
SET NOCOUNT ON
DECLARE @ObjectName NVARCHAR(128)
DECLARE @ScriptMsg NVARCHAR(512)
DECLARE @DBName SYSNAME
DECLARE @ServerName SYSNAME
DECLARE @CurrentObjectName NVARCHAR(128)
DECLARE @RowsCnt INT
DECLARE @CurrentIdx INT
DECLARE @tmpStr NVARCHAR(256)
DECLARE @RowStr NVARCHAR(256)
DECLARE @StartCommentCnt INT
DECLARE @CurrentDBNameIdx INT
DECLARE @CurrentDBName NVARCHAR(128)
DECLARE @CurrentObjectOwner NVARCHAR(128)
DECLARE @DBCnt INT
SET @ObjectName = 'sysobjects'
-- strip out the '[' and ']' characters from the table and column names
IF RIGHT(@ObjectName, 1) = ']'
SET @ObjectName = LEFT(@ObjectName, LEN(@ObjectName) - 1)
IF LEFT(@ObjectName, 1) = '['
SET @ObjectName = RIGHT(@ObjectName, LEN(@ObjectName) - 1)
-- Get the DB and server information
SET @ServerName = CAST(SERVERPROPERTY('MachineName') AS SYSNAME)
SET @DBName = db_name()
-- Check that the provided object name indeed exist in the database
IF @ObjectName IS NULL OR @ObjectName = ''
BEGIN
SET @ScriptMsg = 'The object name provided in the variable @ObjectName is either null or empty. Please provide a valid column name.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
IF NOT EXISTS(
SELECT *
FROM sysobjects WITH (NOLOCK)
WHERE [name] = @ObjectName)
BEGIN
SET @ScriptMsg = N'The object ' + @ObjectName + N' does not exists in the ' + @DBName + N' database, on the ' + @ServerName + N' server.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- Define the temp tables that will hold the column references within the database
IF OBJECT_ID('tempdb..#tblDatabases', 'U') IS NOT NULL
DROP TABLE #tblDatabases
IF OBJECT_ID('tempdb..#tblDependentObject', 'U') IS NOT NULL
DROP TABLE #tblDependentObject
IF OBJECT_ID('tempdb..#tblTextEdit', 'U') IS NOT NULL
DROP TABLE #tblTextEdit
CREATE TABLE #tblDatabases (Idx INT IDENTITY(1, 1), DBName NVARCHAR(128))
CREATE TABLE #tblDependentObject (
DatabaseName NVARCHAR(128),
ObjectName NVARCHAR(128),
ObjectType NVARCHAR(128),
ObjectOwner NVARCHAR(128),
ObjectID INT,
ParentObject NVARCHAR(128))
-- This tables holds the text of the code of the dependent objects scanned.
-- SQLText is 512 and not 256 (as dictated by the sp_helptext command)
-- due to the string processing performed below.
CREATE TABLE #tblTextEdit (Idx INT IDENTITY(1, 1), SQLText NVARCHAR(512))
-- Get all databases on the server
INSERT INTO #tblDatabases (DBName)
SELECT name
FROM master.dbo.sysdatabases WITH (NOLOCK)
SET @DBCnt = @@ROWCOUNT
SET @CurrentDBNameIdx = 1
WHILE @CurrentDBNameIdx <= @DBCnt
BEGIN
SELECT @CurrentDBName = DBName
FROM #tblDatabases
WHERE Idx = @CurrentDBNameIdx
-- First, get all candidate objects: These objects are ones
-- that contain the object name in any form,
-- i.e., ones that contain '%%'
INSERT INTO #tblDependentObject (
DatabaseName,
ObjectName,
ObjectID,
ObjectType,
ObjectOwner,
ParentObject)
EXEC(' USE [' + @CurrentDBName + ']
SELECT DISTINCT N''' + @CurrentDBName + ''' AS DBName,
OBJECT_NAME(a.[id]),
a.[id],
b.xtype,
USER_NAME(b.uid),
CASE
WHEN b.parent_obj <= 0 OR b.parent_obj IS NULL THEN ''--''
ELSE OBJECT_NAME(parent_obj)
END
FROM syscomments a WITH (NOLOCK)
INNER JOIN sysobjects b WITH (NOLOCK)
ON a.id = b.id
WHERE ([text] LIKE N''%' + @ObjectName + N'%'')
and USER_NAME(b.uid) <> ''system_function_schema'' ')
SET @CurrentDBNameIdx = @CurrentDBNameIdx + 1
END
-- For each database that possibly references the object, we run the following code:
-- For each candidate object - perform a thorough analysis:
-- 1. Strip out all the comments in the code
-- 2. Check that only valid characters that may follow/preceed the table and
-- column names indeed do so. This is used to minimize false positives.
SET @CurrentDBNameIdx = 1
SET @CurrentDBName = NULL
WHILE @CurrentDBNameIdx <= @DBCnt
BEGIN
SELECT @CurrentDBName = DBName
FROM #tblDatabases
WHERE Idx = @CurrentDBNameIdx
SELECT @CurrentObjectName = MIN(ObjectName)
FROM #tblDependentObject WITH (NOLOCK)
WHERE DatabaseName = @CurrentDBName
SET @CurrentObjectOwner = NULL
SELECT @CurrentObjectOwner = ObjectOwner
FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
AND DatabaseName = @CurrentDBName
-- Search the text of each referencing database object
WHILE @CurrentObjectName IS NOT NULL
BEGIN
IF @CurrentObjectOwner IS NOT NULL
INSERT INTO #tblTextEdit (SQLText)
EXEC (' USE [' + @CurrentDBName + '] EXEC sp_helptext ''[' + @CurrentObjectOwner + '].[' + @CurrentObjectName + ']'' ')
ELSE
INSERT INTO #tblTextEdit (SQLText)
EXEC (' USE [' + @CurrentDBName + '] EXEC sp_helptext ''[' + @CurrentObjectName + ']'' ')
SET @RowsCnt = @@ROWCOUNT
-- Now, we gather information about all '/*' and '*/' instances in the code,
-- and try to remove all these comments from the code.
-- This is not simple to do, especially not in a recordset friendly manner,
-- since it is possible that there are nested /* comments that start in the same
-- line (returned from sp_helptext), or are spanned over several lines.
-- The easiest way to do it, although tedious, is to scan all text entries in the
-- code. Every instace of /* increments a counter by 1, every instance of
-- */ decrements the counter by 1. The code between /* and */ is not considered
-- for dependency detection.
-- Find the initial scan position
SET @CurrentIdx = NULL
SET @StartCommentCnt = 0
SELECT TOP 1 @CurrentIdx = Idx,
@tmpStr = SQLText
FROM #tblTextEdit WITH (NOLOCK)
WHERE SQLText LIKE '%/*%'
ORDER BY Idx ASC
-- If the code does not contains any /* */ comments - don't get into the
-- while loop below. The purpose of this while loop is to filter
-- all comments that are bounded by /* */ characters.
IF @CurrentIdx IS NULL
SET @CurrentIdx = @RowsCnt + 1
-- Some explanations about this loop: From the first row that contains
-- the /* characters, every time we see a /* we increment a couter by 1.
-- While the counter is > 0 then we are inside a comment. The counter
-- is decremented upon each */, so that nested comments are supported.
-- This way, @tmpStr is used to process the text in each code row,
-- and @RowStr is the actual non-commented code. Each row in #tblTextEdit
-- is updated with the non-commented row @RowStr, in each loop of the WHILE
-- command, so that the after the loop, #tblTextEdit does no longer contain
-- the code comments of type /* */.
WHILE @CurrentIdx <= @RowsCnt
BEGIN
SET @RowStr = N''
-- Process all characters in current text line.
WHILE @tmpStr <> '' AND @tmpStr IS NOT NULL
BEGIN
IF LEFT(@tmpStr, 2) = '/*'
BEGIN
SET @StartCommentCnt = @StartCommentCnt + 1
SET @tmpStr = SUBSTRING(@tmpStr, 3, LEN(@tmpStr))
SET @RowStr = @RowStr + N' '
END
ELSE
BEGIN
IF LEFT(@tmpStr, 2) = '*/'
BEGIN
SET @tmpStr = SUBSTRING(@tmpStr, 3, LEN(@tmpStr))
IF @StartCommentCnt > 0
BEGIN -- the first line is new
SET @RowStr = @RowStr + N' '
SET @StartCommentCnt = @StartCommentCnt - 1
END
END
ELSE
BEGIN
IF @StartCommentCnt = 0
SET @RowStr = @RowStr + SUBSTRING(@tmpStr, 1, 1)
SET @tmpStr = SUBSTRING(@tmpStr, 2, LEN(@tmpStr))
END
END
END
-- Update the processed row without its comments
UPDATE #tblTextEdit
SET SQLText = @RowStr
WHERE Idx = @CurrentIdx
-- Start processing the row
SET @CurrentIdx = @CurrentIdx + 1
SELECT TOP 1 @tmpStr = SQLText
FROM #tblTextEdit WITH (NOLOCK)
WHERE Idx = @CurrentIdx
END
-- Now, we don't strip out comments that start with -- since those
-- can be referenced within PRINT or EXEC statements which may be needed.
-- This may result in false positives, but that is acceptible, given our
-- objective.
-- Last, it is possible that a word is cut off between consecutive lines in
-- sp_helptext. For this reason, if a row in #tblTextEdit ends with a character
-- then we append the first set of characters from the next line to it.
UPDATE a
SET a.SQLText = CASE WHEN LEN(b.SQLText) < 255 THEN a.SQLText + b.SQLText
WHEN CHARINDEX(' ', b.SQLText) > 0 THEN a.SQLText + LEFT(b.SQLText, CHARINDEX(' ', b.SQLText) - 1)
ELSE a.SQLText
END
FROM #tblTextEdit a
INNER JOIN #tblTextEdit b
ON a.Idx + 1 = b.Idx
WHERE SUBSTRING(a.SQLText, 255, 1) <> ''
AND (LEN(b.SQLText) < 255 OR CHARINDEX(' ', b.SQLText) > 0)
-- Finally, we have the raw code. Now, if the column is still referenced
-- inside the code, and is preceeded/followed by valid characters only,
-- then the object is reported as one that references the table column.
--
-- Characters that may preceed an object name are:
-- CHAR(0)-CHAR(31), ' ' (i.e., CHAR(32)), ! (i.e., CHAR(33)), '"' (i.e., CHAR(34)),
-- '%' (i.e., CHAR(37)), '&' (i.e., CHAR(38)), ''' (i.e., CHAR(39)),
-- '(' (i.e., CHAR(40)), ')' (i.e., CHAR(41)), '+' (i.e., CHAR(43)),
-- ',' (i.e., CHAR(44)), '-' (i.e., CHAR(45)), '.' (i.e., CHAR(46)),
-- '<' (i.e., CHAR(60)), '=' (i.e., CHAR(61)), '>' (i.e., CHAR(62)),
-- '[' (i.e., CHAR(91)), '^' (i.e., CHAR(94)), '|' (i.e., CHAR(124))
--
-- After an object name, we can only have one of the following characters:
-- CHAR(0)-CHAR(31), ' ' (i.e., CHAR(32)), '"' (i.e., CHAR(34))
-- '%' (i.e., CHAR(37)), ''' (i.e., CHAR(39)), ')' (i.e., CHAR(41)),
-- '+' (i.e., CHAR(43)), ',' (i.e., CHAR(44)), '-' (i.e., CHAR(45)),
-- '.' (i.e., CHAR(46)), ';' (i.e., CHAR(59)), '<' (i.e., CHAR(60)),
-- '=' (i.e., CHAR(61)), '>' (i.e., CHAR(62)), ']' (i.e., CHAR(93)),
-- '^' (i.e., CHAR(94)), '|' (i.e., CHAR(124))
--
-- Note: CHAR(93) cannot be used in a straightforward manner, hence the query below.
IF NOT EXISTS(
SELECT *
FROM #tblTextEdit WITH (NOLOCK)
WHERE (SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName
+ N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(34) + CHAR(37) + CHAR(39) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(59) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(94) + CHAR(124) + N']' + N'%'
OR SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName
OR SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName + ']%'))
BEGIN
DELETE FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
END
SELECT @CurrentObjectName = MIN(ObjectName)
FROM #tblDependentObject WITH (NOLOCK)
WHERE ObjectName > @CurrentObjectName
AND DatabaseName = @CurrentDBName
SET @CurrentObjectOwner = NULL
SELECT @CurrentObjectOwner = ObjectOwner
FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
AND DatabaseName = @CurrentDBName
-- To keep things clean, start the identity in #tblTextEdit from 1
-- We use DELETE and not TRUNCATE, since the user may not have
-- TRUNCATE permissions.
DELETE FROM #tblTextEdit
DBCC CHECKIDENT('#tblTextEdit', RESEED, 0)
END
SET @CurrentDBNameIdx = @CurrentDBNameIdx + 1
END
UPDATE #tblDependentObject
SET ObjectType = CASE
WHEN ObjectType = 'C' THEN 'CHECK constraint'
WHEN ObjectType = 'D' THEN 'Default or DEFAULT constraint'
WHEN ObjectType = 'F' THEN 'FOREIGN KEY constraint'
WHEN ObjectType = 'L' THEN 'Log'
WHEN ObjectType = 'FN' THEN 'Scalar function'
WHEN ObjectType = 'IF' THEN 'Inlined table-function'
WHEN ObjectType = 'P' THEN 'Stored procedure'
WHEN ObjectType = 'PK' THEN 'PRIMARY KEY constraint'
WHEN ObjectType = 'RF' THEN 'Replication filter stored procedure'
WHEN ObjectType = 'S' THEN 'System table'
WHEN ObjectType = 'TF' THEN 'Table function'
WHEN ObjectType = 'TR' THEN 'Trigger'
WHEN ObjectType = 'U' THEN 'User table'
WHEN ObjectType = 'UQ' THEN 'UNIQUE constraint'
WHEN ObjectType = 'V' THEN 'View'
WHEN ObjectType = 'X' THEN 'Extended stored procedure'
ELSE 'Unknown'
END
SET NOCOUNT OFF
SELECT DatabaseName,
ObjectName,
ObjectType,
ObjectID,
ParentObject
FROM #tblDependentObject WITH (NOLOCK)
ORDER BY 1 ASC
GO
-- This script should be run on a database where a searched object resides.
-- The script then looks at all the code on the current database and all other
-- databases on the server, and searches for all references to the object.
SET NOCOUNT ON
DECLARE @ObjectName NVARCHAR(128)
DECLARE @ScriptMsg NVARCHAR(512)
DECLARE @DBName SYSNAME
DECLARE @ServerName SYSNAME
DECLARE @CurrentObjectName NVARCHAR(128)
DECLARE @RowsCnt INT
DECLARE @CurrentIdx INT
DECLARE @tmpStr NVARCHAR(256)
DECLARE @RowStr NVARCHAR(256)
DECLARE @StartCommentCnt INT
DECLARE @CurrentDBNameIdx INT
DECLARE @CurrentDBName NVARCHAR(128)
DECLARE @CurrentObjectOwner NVARCHAR(128)
DECLARE @DBCnt INT
SET @ObjectName = 'sysobjects'
-- strip out the '[' and ']' characters from the table and column names
IF RIGHT(@ObjectName, 1) = ']'
SET @ObjectName = LEFT(@ObjectName, LEN(@ObjectName) - 1)
IF LEFT(@ObjectName, 1) = '['
SET @ObjectName = RIGHT(@ObjectName, LEN(@ObjectName) - 1)
-- Get the DB and server information
SET @ServerName = CAST(SERVERPROPERTY('MachineName') AS SYSNAME)
SET @DBName = db_name()
-- Check that the provided object name indeed exist in the database
IF @ObjectName IS NULL OR @ObjectName = ''
BEGIN
SET @ScriptMsg = 'The object name provided in the variable @ObjectName is either null or empty. Please provide a valid column name.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
IF NOT EXISTS(
SELECT *
FROM sysobjects WITH (NOLOCK)
WHERE [name] = @ObjectName)
BEGIN
SET @ScriptMsg = N'The object ' + @ObjectName + N' does not exists in the ' + @DBName + N' database, on the ' + @ServerName + N' server.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- Define the temp tables that will hold the column references within the database
IF OBJECT_ID('tempdb..#tblDatabases', 'U') IS NOT NULL
DROP TABLE #tblDatabases
IF OBJECT_ID('tempdb..#tblDependentObject', 'U') IS NOT NULL
DROP TABLE #tblDependentObject
IF OBJECT_ID('tempdb..#tblTextEdit', 'U') IS NOT NULL
DROP TABLE #tblTextEdit
CREATE TABLE #tblDatabases (Idx INT IDENTITY(1, 1), DBName NVARCHAR(128))
CREATE TABLE #tblDependentObject (
DatabaseName NVARCHAR(128),
ObjectName NVARCHAR(128),
ObjectType NVARCHAR(128),
ObjectOwner NVARCHAR(128),
ObjectID INT,
ParentObject NVARCHAR(128))
-- This tables holds the text of the code of the dependent objects scanned.
-- SQLText is 512 and not 256 (as dictated by the sp_helptext command)
-- due to the string processing performed below.
CREATE TABLE #tblTextEdit (Idx INT IDENTITY(1, 1), SQLText NVARCHAR(512))
-- Get all databases on the server
INSERT INTO #tblDatabases (DBName)
SELECT name
FROM master.dbo.sysdatabases WITH (NOLOCK)
SET @DBCnt = @@ROWCOUNT
SET @CurrentDBNameIdx = 1
WHILE @CurrentDBNameIdx <= @DBCnt
BEGIN
SELECT @CurrentDBName = DBName
FROM #tblDatabases
WHERE Idx = @CurrentDBNameIdx
-- First, get all candidate objects: These objects are ones
-- that contain the object name in any form,
-- i.e., ones that contain '%
INSERT INTO #tblDependentObject (
DatabaseName,
ObjectName,
ObjectID,
ObjectType,
ObjectOwner,
ParentObject)
EXEC(' USE [' + @CurrentDBName + ']
SELECT DISTINCT N''' + @CurrentDBName + ''' AS DBName,
OBJECT_NAME(a.[id]),
a.[id],
b.xtype,
USER_NAME(b.uid),
CASE
WHEN b.parent_obj <= 0 OR b.parent_obj IS NULL THEN ''--''
ELSE OBJECT_NAME(parent_obj)
END
FROM syscomments a WITH (NOLOCK)
INNER JOIN sysobjects b WITH (NOLOCK)
ON a.id = b.id
WHERE ([text] LIKE N''%' + @ObjectName + N'%'')
and USER_NAME(b.uid) <> ''system_function_schema'' ')
SET @CurrentDBNameIdx = @CurrentDBNameIdx + 1
END
-- For each database that possibly references the object, we run the following code:
-- For each candidate object - perform a thorough analysis:
-- 1. Strip out all the comments in the code
-- 2. Check that only valid characters that may follow/preceed the table and
-- column names indeed do so. This is used to minimize false positives.
SET @CurrentDBNameIdx = 1
SET @CurrentDBName = NULL
WHILE @CurrentDBNameIdx <= @DBCnt
BEGIN
SELECT @CurrentDBName = DBName
FROM #tblDatabases
WHERE Idx = @CurrentDBNameIdx
SELECT @CurrentObjectName = MIN(ObjectName)
FROM #tblDependentObject WITH (NOLOCK)
WHERE DatabaseName = @CurrentDBName
SET @CurrentObjectOwner = NULL
SELECT @CurrentObjectOwner = ObjectOwner
FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
AND DatabaseName = @CurrentDBName
-- Search the text of each referencing database object
WHILE @CurrentObjectName IS NOT NULL
BEGIN
IF @CurrentObjectOwner IS NOT NULL
INSERT INTO #tblTextEdit (SQLText)
EXEC (' USE [' + @CurrentDBName + '] EXEC sp_helptext ''[' + @CurrentObjectOwner + '].[' + @CurrentObjectName + ']'' ')
ELSE
INSERT INTO #tblTextEdit (SQLText)
EXEC (' USE [' + @CurrentDBName + '] EXEC sp_helptext ''[' + @CurrentObjectName + ']'' ')
SET @RowsCnt = @@ROWCOUNT
-- Now, we gather information about all '/*' and '*/' instances in the code,
-- and try to remove all these comments from the code.
-- This is not simple to do, especially not in a recordset friendly manner,
-- since it is possible that there are nested /* comments that start in the same
-- line (returned from sp_helptext), or are spanned over several lines.
-- The easiest way to do it, although tedious, is to scan all text entries in the
-- code. Every instace of /* increments a counter by 1, every instance of
-- */ decrements the counter by 1. The code between /* and */ is not considered
-- for dependency detection.
-- Find the initial scan position
SET @CurrentIdx = NULL
SET @StartCommentCnt = 0
SELECT TOP 1 @CurrentIdx = Idx,
@tmpStr = SQLText
FROM #tblTextEdit WITH (NOLOCK)
WHERE SQLText LIKE '%/*%'
ORDER BY Idx ASC
-- If the code does not contains any /* */ comments - don't get into the
-- while loop below. The purpose of this while loop is to filter
-- all comments that are bounded by /* */ characters.
IF @CurrentIdx IS NULL
SET @CurrentIdx = @RowsCnt + 1
-- Some explanations about this loop: From the first row that contains
-- the /* characters, every time we see a /* we increment a couter by 1.
-- While the counter is > 0 then we are inside a comment. The counter
-- is decremented upon each */, so that nested comments are supported.
-- This way, @tmpStr is used to process the text in each code row,
-- and @RowStr is the actual non-commented code. Each row in #tblTextEdit
-- is updated with the non-commented row @RowStr, in each loop of the WHILE
-- command, so that the after the loop, #tblTextEdit does no longer contain
-- the code comments of type /* */.
WHILE @CurrentIdx <= @RowsCnt
BEGIN
SET @RowStr = N''
-- Process all characters in current text line.
WHILE @tmpStr <> '' AND @tmpStr IS NOT NULL
BEGIN
IF LEFT(@tmpStr, 2) = '/*'
BEGIN
SET @StartCommentCnt = @StartCommentCnt + 1
SET @tmpStr = SUBSTRING(@tmpStr, 3, LEN(@tmpStr))
SET @RowStr = @RowStr + N' '
END
ELSE
BEGIN
IF LEFT(@tmpStr, 2) = '*/'
BEGIN
SET @tmpStr = SUBSTRING(@tmpStr, 3, LEN(@tmpStr))
IF @StartCommentCnt > 0
BEGIN -- the first line is new
SET @RowStr = @RowStr + N' '
SET @StartCommentCnt = @StartCommentCnt - 1
END
END
ELSE
BEGIN
IF @StartCommentCnt = 0
SET @RowStr = @RowStr + SUBSTRING(@tmpStr, 1, 1)
SET @tmpStr = SUBSTRING(@tmpStr, 2, LEN(@tmpStr))
END
END
END
-- Update the processed row without its comments
UPDATE #tblTextEdit
SET SQLText = @RowStr
WHERE Idx = @CurrentIdx
-- Start processing the row
SET @CurrentIdx = @CurrentIdx + 1
SELECT TOP 1 @tmpStr = SQLText
FROM #tblTextEdit WITH (NOLOCK)
WHERE Idx = @CurrentIdx
END
-- Now, we don't strip out comments that start with -- since those
-- can be referenced within PRINT or EXEC statements which may be needed.
-- This may result in false positives, but that is acceptible, given our
-- objective.
-- Last, it is possible that a word is cut off between consecutive lines in
-- sp_helptext. For this reason, if a row in #tblTextEdit ends with a character
-- then we append the first set of characters from the next line to it.
UPDATE a
SET a.SQLText = CASE WHEN LEN(b.SQLText) < 255 THEN a.SQLText + b.SQLText
WHEN CHARINDEX(' ', b.SQLText) > 0 THEN a.SQLText + LEFT(b.SQLText, CHARINDEX(' ', b.SQLText) - 1)
ELSE a.SQLText
END
FROM #tblTextEdit a
INNER JOIN #tblTextEdit b
ON a.Idx + 1 = b.Idx
WHERE SUBSTRING(a.SQLText, 255, 1) <> ''
AND (LEN(b.SQLText) < 255 OR CHARINDEX(' ', b.SQLText) > 0)
-- Finally, we have the raw code. Now, if the column is still referenced
-- inside the code, and is preceeded/followed by valid characters only,
-- then the object is reported as one that references the table column.
--
-- Characters that may preceed an object name are:
-- CHAR(0)-CHAR(31), ' ' (i.e., CHAR(32)), ! (i.e., CHAR(33)), '"' (i.e., CHAR(34)),
-- '%' (i.e., CHAR(37)), '&' (i.e., CHAR(38)), ''' (i.e., CHAR(39)),
-- '(' (i.e., CHAR(40)), ')' (i.e., CHAR(41)), '+' (i.e., CHAR(43)),
-- ',' (i.e., CHAR(44)), '-' (i.e., CHAR(45)), '.' (i.e., CHAR(46)),
-- '<' (i.e., CHAR(60)), '=' (i.e., CHAR(61)), '>' (i.e., CHAR(62)),
-- '[' (i.e., CHAR(91)), '^' (i.e., CHAR(94)), '|' (i.e., CHAR(124))
--
-- After an object name, we can only have one of the following characters:
-- CHAR(0)-CHAR(31), ' ' (i.e., CHAR(32)), '"' (i.e., CHAR(34))
-- '%' (i.e., CHAR(37)), ''' (i.e., CHAR(39)), ')' (i.e., CHAR(41)),
-- '+' (i.e., CHAR(43)), ',' (i.e., CHAR(44)), '-' (i.e., CHAR(45)),
-- '.' (i.e., CHAR(46)), ';' (i.e., CHAR(59)), '<' (i.e., CHAR(60)),
-- '=' (i.e., CHAR(61)), '>' (i.e., CHAR(62)), ']' (i.e., CHAR(93)),
-- '^' (i.e., CHAR(94)), '|' (i.e., CHAR(124))
--
-- Note: CHAR(93) cannot be used in a straightforward manner, hence the query below.
IF NOT EXISTS(
SELECT *
FROM #tblTextEdit WITH (NOLOCK)
WHERE (SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName
+ N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(34) + CHAR(37) + CHAR(39) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(59) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(94) + CHAR(124) + N']' + N'%'
OR SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName
OR SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName + ']%'))
BEGIN
DELETE FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
END
SELECT @CurrentObjectName = MIN(ObjectName)
FROM #tblDependentObject WITH (NOLOCK)
WHERE ObjectName > @CurrentObjectName
AND DatabaseName = @CurrentDBName
SET @CurrentObjectOwner = NULL
SELECT @CurrentObjectOwner = ObjectOwner
FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
AND DatabaseName = @CurrentDBName
-- To keep things clean, start the identity in #tblTextEdit from 1
-- We use DELETE and not TRUNCATE, since the user may not have
-- TRUNCATE permissions.
DELETE FROM #tblTextEdit
DBCC CHECKIDENT('#tblTextEdit', RESEED, 0)
END
SET @CurrentDBNameIdx = @CurrentDBNameIdx + 1
END
UPDATE #tblDependentObject
SET ObjectType = CASE
WHEN ObjectType = 'C' THEN 'CHECK constraint'
WHEN ObjectType = 'D' THEN 'Default or DEFAULT constraint'
WHEN ObjectType = 'F' THEN 'FOREIGN KEY constraint'
WHEN ObjectType = 'L' THEN 'Log'
WHEN ObjectType = 'FN' THEN 'Scalar function'
WHEN ObjectType = 'IF' THEN 'Inlined table-function'
WHEN ObjectType = 'P' THEN 'Stored procedure'
WHEN ObjectType = 'PK' THEN 'PRIMARY KEY constraint'
WHEN ObjectType = 'RF' THEN 'Replication filter stored procedure'
WHEN ObjectType = 'S' THEN 'System table'
WHEN ObjectType = 'TF' THEN 'Table function'
WHEN ObjectType = 'TR' THEN 'Trigger'
WHEN ObjectType = 'U' THEN 'User table'
WHEN ObjectType = 'UQ' THEN 'UNIQUE constraint'
WHEN ObjectType = 'V' THEN 'View'
WHEN ObjectType = 'X' THEN 'Extended stored procedure'
ELSE 'Unknown'
END
SET NOCOUNT OFF
SELECT DatabaseName,
ObjectName,
ObjectType,
ObjectID,
ParentObject
FROM #tblDependentObject WITH (NOLOCK)
ORDER BY 1 ASC
GO
List objects per filegroup
http://www.tsql.ca/Resources/Articles/Listobjectsperfilegroup/tabid/607/Default.aspx
The following script lists link between database objects and filegroups. You can also use this script to identify filegroup with no objects associated
SQL2000:
==========
IF OBJECT_ID('tempdb.dbo.#FileGroup') IS NOT NULL
DROP TABLE #FileGroup
IF OBJECT_ID('tempdb.dbo.#ObjectFileGroup') IS NOT NULL
DROP TABLE #ObjectFileGroup
CREATE TABLE #FileGroup (
FileGroup sysname
)
CREATE TABLE #ObjectFileGroup (
ObjectName sysname,
ObjectType varchar(20),
FileGroupID int,
FileGroup sysname
)
SET NOCOUNT ON
DECLARE @TableName sysname
DECLARE @id int
DECLARE cur_Tables CURSOR FAST_FORWARD FOR
SELECT TableName = [name], id FROM dbo.sysobjects WHERE type = 'U'
OPEN cur_Tables
FETCH NEXT FROM cur_Tables INTO @TableName, @id
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #FileGroup
INSERT #FileGroup (FileGroup)
EXEC sp_objectfilegroup @id
INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)
SELECT @TableName, 'TABLE', FILEGROUP_ID(FileGroup), FileGroup
FROM #FileGroup
FETCH NEXT FROM cur_Tables INTO @TableName, @id
END
CLOSE cur_Tables
DEALLOCATE cur_Tables
INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)
SELECT OBJECT_NAME(id) + ' * ' +[name], 'INDEX', groupid, FILEGROUP_NAME(groupid) FROM dbo.sysindexes
WHERE FILEGROUP_NAME(groupid) IS NOT NULL
AND OBJECT_NAME(id) NOT LIKE 'sys%'
AND [name] NOT LIKE '_WA_Sys%'
AND [name] NOT LIKE 'Statistic_%'
SELECT FileGroupName = FILEGROUP_NAME(sf.groupid),/*ofg.FileGroup, */ofg.ObjectName, ofg.ObjectType, FileName = sf.filename, FileSize = sf.[size] / 128
FROM #ObjectFileGroup ofg
RIGHT JOIN dbo.sysfiles sf
ON ofg.FileGroupID = sf.groupid
ORDER BY FileGroup, ObjectName
SQL 2005 (very simplified):
===========================
SELECT
fg.data_space_id, fg.name,
ObjectName = OBJECT_NAME(p.object_id), p.index_id
,df.name, df.physical_name, [Size] = df.size*8/1024
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)
The following script lists link between database objects and filegroups. You can also use this script to identify filegroup with no objects associated
SQL2000:
==========
IF OBJECT_ID('tempdb.dbo.#FileGroup') IS NOT NULL
DROP TABLE #FileGroup
IF OBJECT_ID('tempdb.dbo.#ObjectFileGroup') IS NOT NULL
DROP TABLE #ObjectFileGroup
CREATE TABLE #FileGroup (
FileGroup sysname
)
CREATE TABLE #ObjectFileGroup (
ObjectName sysname,
ObjectType varchar(20),
FileGroupID int,
FileGroup sysname
)
SET NOCOUNT ON
DECLARE @TableName sysname
DECLARE @id int
DECLARE cur_Tables CURSOR FAST_FORWARD FOR
SELECT TableName = [name], id FROM dbo.sysobjects WHERE type = 'U'
OPEN cur_Tables
FETCH NEXT FROM cur_Tables INTO @TableName, @id
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #FileGroup
INSERT #FileGroup (FileGroup)
EXEC sp_objectfilegroup @id
INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)
SELECT @TableName, 'TABLE', FILEGROUP_ID(FileGroup), FileGroup
FROM #FileGroup
FETCH NEXT FROM cur_Tables INTO @TableName, @id
END
CLOSE cur_Tables
DEALLOCATE cur_Tables
INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)
SELECT OBJECT_NAME(id) + ' * ' +[name], 'INDEX', groupid, FILEGROUP_NAME(groupid) FROM dbo.sysindexes
WHERE FILEGROUP_NAME(groupid) IS NOT NULL
AND OBJECT_NAME(id) NOT LIKE 'sys%'
AND [name] NOT LIKE '_WA_Sys%'
AND [name] NOT LIKE 'Statistic_%'
SELECT FileGroupName = FILEGROUP_NAME(sf.groupid),/*ofg.FileGroup, */ofg.ObjectName, ofg.ObjectType, FileName = sf.filename, FileSize = sf.[size] / 128
FROM #ObjectFileGroup ofg
RIGHT JOIN dbo.sysfiles sf
ON ofg.FileGroupID = sf.groupid
ORDER BY FileGroup, ObjectName
SQL 2005 (very simplified):
===========================
SELECT
fg.data_space_id, fg.name,
ObjectName = OBJECT_NAME(p.object_id), p.index_id
,df.name, df.physical_name, [Size] = df.size*8/1024
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)
The Best Script for --Move a table between filegroups
http://www.tsql.ca/Resources/Articles/MoveTablesbetweenFilegroups/tabid/610/Default.aspx
Sometimes we need to move tables between filegroups. There are two options how to do it.
The first option is to rename existing table, create a new one with the same structure on the desired filegroup(s), copy the data over and then to drop the original table. It requires log space, some outage for the system, risk of having problems with security, and so on. But this is the only option if table has BLOB fields.
The second option is to recreate table’s clustered index on the desired filegroup. It will move the table itself on that filegroup. As it was mentioned the BLOBs field will have problems – they are stored separately from the table’s data pages and are not affected by any index operation.I had to reallocate all the tables in multiple databases, to put them into new filegroups.
Since option #1 was not good for some reasons I implemented such operation as a script. Script moves tables to proper filegroup if it's not aligned with desired filegroup specified in the configuration list. Script may operate with multiple databases at once, so one may specify tables from different databases.
Script structure:
A configuration list (database, table, filegroup) is created.
Existence of all filegroups is checked - quits if there is an assumed filegroup that does not exist in the database.
Every table in the database is checked against that list.
For those having different filegroup a script is built and preserved: that script re-creates existing indexes (creates with drop existing) I.e. it repeats existing indexes for the table with the new filegroup specified. Since some of the non-clustered indexes may be located in a different filegroup, they are moved to the table's filegroup. See index (re-)creation script description below.
Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
compose drop index commands list create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup.
compose create index (with drop existing) commands for the clustered indexes that are to be moved. For tables without any clustered index the PK supporting index is made clustered and recreated with other.
compose create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup. The index creation command does not include filegroup name, so it will be created in the same filegroup as the table it belongs to.
Those commands are saved in a table with following structure:
record id (identity)
table name
sequence number (the same as topics listed just above)
index name
sql command
The very structure manipulation script will go through that table in order of table name then sequence number then index name (latter is just for convinience - to analyze the log). There will be a transaction for each table - too costly to make it around everything.
The script comments verbosely its execution process at the run time.
Script is also is assumed to be a tool for operational DBA team parameterized by the table with tables/filegroups (see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script output must be directed to text or a file.
/*******************************************************************************
** Name: Database tables moved to proper filegroups.sql
** Description: Script moves tables to proper filegroup if it's not aligned
with a list which is derived from the model(s).
Script structure:
- A list (database, table, filegroup) is created.
- Existence of all filegroups is checked - quits if there is an
assumed filegroup that does not exist in the database.
- Every table in the database is checked against that list.
- For those having different filegroup a script is built
and preserved: that script re-creates existing indexes
(creates with drop existing)
I.e. it repeats existing indexes
for the table with the new filegroup specified.
Since some of the non-clustered indexes may be located in
a different filegroup, they are moved to the table's filegroup.
See index (re-)creation script description below.
- Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
1) compose drop index commands list create index commands list
after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup.
2) compose create index (with drop existing) commands for the clustered
indexes that are to be moved. For tables without any clustered
index the PK supporting index is made clustered and recreated with other.
3) compose create index commands list after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup. The index creation command does not include
filegroup name, so it will be created in the same filegroup
as the table it belongs to.
Those commands are saved in a table with following structure:
- record id (identity)
- table name
- sequence number (the same as topics listed just above)
- index name
- sql command
The very structure manipulation script will go through that table
in order of table name then sequence number then index name
(latter is just for convinience - to analyze the log).
There will be a transaction for each table - too costly to make
it around everything.
Script comments verbosely its execution process at the run time.
Script is also is assumed to be a tool for operations team
parameterized by the table with tables/filegroups
(see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script
output must be directed to text or a file.
**
** Input Parameters: database/table/filegroup mapping in a form
of insert statements (see configurable part below).
** Returns: a table with database/table/index names with filegroup names
for each object: original one, configured(desired) one and the final one,
status of the operation for each index is specified.
** Modification History
** Sr Date Modified By Reason
** 1 05/01/2007 Alexander Karmanov Initial version
*******************************************************************************/
use master
go
set nocount on
declare
@script_name sysname
select
@script_name = 'Database tables moved to proper filegroups.sql'
print replicate('-', 80)
print 'Script name: ' @script_name
print 'Server: ' @@servername
print 'Database: ' db_name()
print 'User: ' suser_sname()
print 'Start time: ' cast(getdate() as varchar)
print replicate('-', 80)
go
/******************************************************************************
Here is a configurable part:
mapping between tables and filegroups
*******************************************************************************/
-- declare a table with desired database tables layout
if object_id('tempdb..#desired_layout') is not null drop table #desired_layout
create table #desired_layout (database_name sysname, table_name sysname, filegroup_name sysname)
insert into #desired_layout (database_name, table_name, filegroup_name)
-- database_name_1
/* database_name_1 is not moved for a while */
-- database_name_2
select 'database_name_2', 'address_type', 'srv001_db_2_group_1' union all
<....>
select 'database_name_2', 'workflow_status', 'srv001_db_2_group_x'
/******************************************************************************
End of configurable part,
below goes the actual code
*******************************************************************************/
go
use tempdb
go
-- a table with the column list for all the indexes in the source database is prepared,
-- but is left emtpy - it's needed to be created prior to a helper UDF
if object_id('tempdb..index_columns') is not null drop table index_columns
create table index_columns (database_name sysname, table_name sysname, index_name sysname,
column_name sysname, key_no int,
is_index_clustered bit, is_index_unique bit, is_column_descending bit)
go
-- a helper function converting a table with column names into a comma-separated list
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
go
create function dbo.f_get_column_list (
@database_name sysname,
@table_name sysname,
@index_name sysname
)
returns varchar(8000)
as
begin
declare @col_list varchar(8000)
select @col_list = isnull(@col_list ', ', '')
column_name ' '
case is_column_descending when 1 then 'DESC' else '' end
from index_columns
where database_name = @database_name
and table_name = @table_name
and index_name = @index_name
order by key_no
return @col_list
end
go
-- after that point the only 'go' command is the very final one - many variables are reused.
declare
@sql varchar(8000),
@crlf char(2),
@rowcnt int
select
@crlf = char(13) char(10),
@sql = ''
-- check for existence of all the filegroups mentioned in the layout table, exit if there is any problem:
-- get a list of existing filegroups for all the databases:
if object_id('tempdb..#existing_filegroups') is not null drop table #existing_filegroups
create table #existing_filegroups (database_name sysname, filegroup_name sysname)
select @sql = @sql @crlf 'select ''' database_name ''' as database_name, groupname as group_name from '
database_name '.dbo.sysfilegroups union all'
from #desired_layout
group by database_name
select
@sql = replace (@sql ';', 'union all;', '')
insert into #existing_filegroups (database_name, filegroup_name)
exec(@sql)
(select @rowcnt = count(distinct d.filegroup_name)
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null)
if @rowcnt > 0
begin
select 'In ' upper(d.database_name) ' database there is no filegroup ''' upper(d.filegroup_name) ''''
as 'Filegroups that does not exist in the target database:'
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null
group by d.database_name, d.filegroup_name
print 'Script is aborted because there is at least one filegroup that does not exist (see above).'
return
end
print 'Check for the missing filegroups completed successfully - all the filegroups mentioned in the list do exist.'
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#existing_layout') is not null drop table #existing_layout
create table #existing_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf
'g2.groupname as table_filegroup_name ' @crlf
' from ' database_name '.dbo.sysobjects o ' @crlf
' join ' database_name '.dbo.sysindexes i ' @crlf
' on o.id = i.id ' @crlf
' join ' database_name '.dbo.sysfilegroups g ' @crlf
' on i.groupid = g.groupid ' @crlf
' left join (select c.id, o2.name ' @crlf
' from ' database_name '.dbo.sysconstraints c ' @crlf
' join ' database_name '.dbo.sysobjects o2 ' @crlf
' on c.constid = o2.id ' @crlf
' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf
' on o.id = k.id ' @crlf
' join ' database_name '.dbo.sysindexes i2 ' @crlf
' on o.id = i2.id ' @crlf
' and i2.indid < 2 ' @crlf
' join ' database_name '.dbo.sysfilegroups g2 ' @crlf
' on i2.groupid = g2.groupid ' @crlf
' where o.xtype = ''U'' ' @crlf
' and i.name not like ''\_WA%'' escape ''\'' ' @crlf
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf
' and i.indid < 255 ' @crlf
from #desired_layout
group by database_name
insert into #existing_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- compose table with commands changing the database structure
if object_id('tempdb..#sql') is not null drop table #sql
create table #sql (rowid int identity (1,1), database_name sysname, table_name sysname,
index_name sysname, seq_number int, sql varchar(7000))
-- get all the indexes that must be dropped:
-- 1. indexes that are not clustered, are not created as PK or UNIQUE constraint.
-- 2. either:
-- 2a. index is located not in a desired filegroup
-- 2b. clustered index on the table (index belongs to) is located not in a desired filegroup
-- All those indexes got sequence code of 1 meaning it will be executed first.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 1 as seq_number,
sql = 'use ' e.database_name '; drop index dbo.' e.table_name '.' e.index_name
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.is_pk_or_uk = 0
and e.ind_id > 1
and (
e.filegroup_name <> d.filegroup_name
or
e.table_filegroup_name <> d.filegroup_name
)
-- get all the indexes that must be created as clustered with drop_existing option:
-- 1. all the indexes that are clustered or PK or unique
-- (all the PK indexes on the tables that don't have clustered key get here as PK/UQ so the distinction needs to be done -
-- when the CLUSTERED word is included.)
-- All those indexes got sequence code of 2 meaning they will be executed when all the dependant indexes are dropped
-- The very command is to be added later - now just identifiers.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 2 as seq_number,
sql = 'use ' e.database_name '; create %unique% %clustered% index ' e.index_name ' on dbo.' e.table_name
'(%column_list%) with fillfactor = 90, drop_existing on ' quotename(d.filegroup_name)
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where
e.is_pk_or_uk = 1 -- check for PK or unique key
or e.ind_id = 1 -- indid in sysindexes means clustered key on table
-- get all the indexes that are dropped in the first step - with the new destination filegroup and
-- all the parameters like fillfactor, and so on.
-- no need to analyze layouts, just take those that were marked as to be dropped and
-- compose create index statement for them.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct s.database_name, s.table_name, s.index_name, 3 as seq_number,
sql = 'use ' s.database_name '; create %unique% nonclustered index ' s.index_name ' on dbo.' s.table_name
'(%column_list%) with fillfactor = 90 on ' quotename(d.filegroup_name)
from #sql s
join #desired_layout d
on s.database_name = d.database_name
and s.table_name = d.table_name
where seq_number = 1
-- get attributes, column lists from the existing database
select @sql = null
select @sql = isnull(@sql @crlf, '')
'use ' database_name @crlf
'insert into tempdb..index_columns (database_name, table_name, index_name, column_name, ' @crlf
' key_no, is_index_clustered, is_index_unique, is_column_descending)' @crlf
'select ''' database_name ''', o.name as table_name, i.name as index_name, c.name as column_name, k.keyno, ' @crlf
' INDEXPROPERTY (o.id, i.name, ''isclustered'') as is_index_clustered,' @crlf
' INDEXPROPERTY (o.id, i.name, ''isunique'') as is_index_unique,' @crlf
' INDEXKEY_PROPERTY(o.id, i.indid, k.keyno, ''isdescending'') as is_column_descending' @crlf
' from ' database_name '.dbo.sysobjects o' @crlf
' join ' database_name '.dbo.sysindexes i' @crlf
' on o.id = i.id' @crlf
' join ' database_name '.dbo.sysindexkeys k' @crlf
' on i.id = k.id' @crlf
' and i.indid = k.indid' @crlf
' join ' database_name '.dbo.syscolumns c' @crlf
' on k.colid = c.colid' @crlf
' and k.id = c.id' @crlf
' where ' @crlf
' o.xtype = ''U''' @crlf
' and INDEXPROPERTY (i.id, i.name, ''isstatistics'') = 0' @crlf
''
from #desired_layout
group by database_name
exec(@sql)
-- replace %column_list% placeholders in the index creation statements with proper column list
update #sql
set sql = replace(sql, '%column_list%', dbo.f_get_column_list(database_name, table_name, index_name))
-- specify proper clustered option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_clustered = 1
-- for indexes that are PK in table without any clustered index - we make PK indexes clustered,
-- so it will be rebuilt in the new (desired) filegroup.
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
join #existing_layout e
on s.database_name = e.database_name
and s.table_name = e.database_name
and e.ind_id = 0 -- that means the table is a heap right now (no clustered index)
-- for the rest set it as nonclustered
update s
set sql = replace(sql, '%clustered%', 'nonclustered')
from #sql s
-- specify proper unique option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%unique%', 'unique')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_unique = 1
-- for the rest set it as non-unique
update s
set sql = replace(sql, '%unique%', '')
from #sql s
-- at this point all the commands are prepared in #sql table
-- and all we need is just to execute them one-by-one.
-- Actions for a table are wrapped into a transaction,
-- so if there is any error no table indexes structures
print cast(getdate() as varchar) ': Starting processing the tables'
declare
@rowid int,
@database_name sysname,
@table_name sysname,
@index_name sysname,
@seq_number int
declare t cursor static for
select distinct database_name, table_name
from #sql
order by database_name, table_name
open t
fetch next from t into @database_name, @table_name
while @@fetch_status = 0
begin
print cast(getdate() as varchar) ': Processing table ' @table_name ' in ' @database_name ' database'
begin tran
declare i cursor static for
select rowid, index_name, sql, seq_number
from #sql
where database_name = @database_name
and table_name = @table_name
order by seq_number, index_name
open i
fetch next from i into @rowid, @index_name, @sql, @seq_number
while @@fetch_status = 0
begin
if @seq_number = 1 print cast(getdate() as varchar) ': Dropping index ' @table_name '.' @index_name
else if @seq_number = 2 print cast(getdate() as varchar) ': Moving index ' @table_name '.' @index_name ' to another filegroup'
else if @seq_number = 3 print cast(getdate() as varchar) ': Re-creating index ' @table_name '.' @index_name
print cast(getdate() as varchar) ': A command to be executed: ' @sql
exec(@sql)
-- if there is an error - stop processing that table, rolling back transaction for it and reporting failure for it
if @@error <> 0
begin
rollback
print cast(getdate() as varchar) ': Command failed to execute'
break
end
print cast(getdate() as varchar) ': Command successfully completed'
fetch next from i into @rowid, @index_name, @sql, @seq_number
end
close i
deallocate i
-- if we have uncommitted transaction here that means no error occured in th loop on the indexes
-- and there is no transaction rollback issued above - i.e. everything is fine for that table,
-- report success on it (and commit it of course):
if @@trancount > 0
begin
commit
print cast(getdate() as varchar) ': Successfully processed table ' @table_name ' in ' @database_name ' database'
end
fetch next from t into @database_name, @table_name
end -- of loop on t cursor
close t
deallocate t
-- get the tables layout after the changes (similar to what was done before the changes:
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#updated_layout') is not null drop table #updated_layout
create table #updated_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf
'g2.groupname as table_filegroup_name ' @crlf
' from ' database_name '.dbo.sysobjects o ' @crlf
' join ' database_name '.dbo.sysindexes i ' @crlf
' on o.id = i.id ' @crlf
' join ' database_name '.dbo.sysfilegroups g ' @crlf
' on i.groupid = g.groupid ' @crlf
' left join (select c.id, o2.name ' @crlf
' from ' database_name '.dbo.sysconstraints c ' @crlf
' join ' database_name '.dbo.sysobjects o2 ' @crlf
' on c.constid = o2.id ' @crlf
' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf
' on o.id = k.id ' @crlf
' join ' database_name '.dbo.sysindexes i2 ' @crlf
' on o.id = i2.id ' @crlf
' and i2.indid < 2 ' @crlf
' join ' database_name '.dbo.sysfilegroups g2 ' @crlf
' on i2.groupid = g2.groupid ' @crlf
' where o.xtype = ''U'' ' @crlf
' and i.name not like ''\_WA%'' escape ''\'' ' @crlf
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf
' and i.indid < 255 ' @crlf
from #desired_layout
group by database_name
insert into #updated_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- report difference between the previously existing layout, what was the intent and what we've got after the update:
print 'Script execution status report (see the grid if it is not displayed below):'
select
e.database_name '.' e.table_name '.' e.index_name as object,
e.filegroup_name as original_fileroup,
isnull(d.filegroup_name, 'NOT CONFIGURED') as desired_new_filegroup,
isnull(u.filegroup_name, 'OBJECT DISAPPEARED!!!') as actual_new_filegroup,
case
when d.filegroup_name is null then 'NOT CONFIGURED'
when u.filegroup_name is null then 'OBJECT WAS LOST'
when d.filegroup_name <> u.filegroup_name then 'NO UPDATE HAPPENED'
else 'ok'
end as update_status
from #existing_layout e
left join #updated_layout u
on e.database_name = u.database_name
and e.table_name = u.table_name
and e.index_name = u.index_name
left join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.ind_id > 0 -- no need to show items for the heap (which item is not actually an index anyway).
-- If such heap table has a PK it is shown in a separate line (with ind_id > 1)
order by e.database_name, e.table_name, e.index_name
go
-- clean-up
use tempdb
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
if object_id('tempdb..index_columns') is not null drop table index_columns
go
-- to release any database we switched to
use master
go
-- the end of the script
/*
select *
from #desired_layout
select *
from #updated_layout
order by table_name
select *
from #existing_layout
order by table_name
select *
from #sql
order by table_name
*/
Sometimes we need to move tables between filegroups. There are two options how to do it.
The first option is to rename existing table, create a new one with the same structure on the desired filegroup(s), copy the data over and then to drop the original table. It requires log space, some outage for the system, risk of having problems with security, and so on. But this is the only option if table has BLOB fields.
The second option is to recreate table’s clustered index on the desired filegroup. It will move the table itself on that filegroup. As it was mentioned the BLOBs field will have problems – they are stored separately from the table’s data pages and are not affected by any index operation.I had to reallocate all the tables in multiple databases, to put them into new filegroups.
Since option #1 was not good for some reasons I implemented such operation as a script. Script moves tables to proper filegroup if it's not aligned with desired filegroup specified in the configuration list. Script may operate with multiple databases at once, so one may specify tables from different databases.
Script structure:
A configuration list (database, table, filegroup) is created.
Existence of all filegroups is checked - quits if there is an assumed filegroup that does not exist in the database.
Every table in the database is checked against that list.
For those having different filegroup a script is built and preserved: that script re-creates existing indexes (creates with drop existing) I.e. it repeats existing indexes for the table with the new filegroup specified. Since some of the non-clustered indexes may be located in a different filegroup, they are moved to the table's filegroup. See index (re-)creation script description below.
Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
compose drop index commands list create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup.
compose create index (with drop existing) commands for the clustered indexes that are to be moved. For tables without any clustered index the PK supporting index is made clustered and recreated with other.
compose create index commands list after existing indexes for non-clustered indexes which are located not in the desired filegroup or if the clustered index for the table is not in the desired filegroup. The index creation command does not include filegroup name, so it will be created in the same filegroup as the table it belongs to.
Those commands are saved in a table with following structure:
record id (identity)
table name
sequence number (the same as topics listed just above)
index name
sql command
The very structure manipulation script will go through that table in order of table name then sequence number then index name (latter is just for convinience - to analyze the log). There will be a transaction for each table - too costly to make it around everything.
The script comments verbosely its execution process at the run time.
Script is also is assumed to be a tool for operational DBA team parameterized by the table with tables/filegroups (see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script output must be directed to text or a file.
/*******************************************************************************
** Name: Database tables moved to proper filegroups.sql
** Description: Script moves tables to proper filegroup if it's not aligned
with a list which is derived from the model(s).
Script structure:
- A list (database, table, filegroup) is created.
- Existence of all filegroups is checked - quits if there is an
assumed filegroup that does not exist in the database.
- Every table in the database is checked against that list.
- For those having different filegroup a script is built
and preserved: that script re-creates existing indexes
(creates with drop existing)
I.e. it repeats existing indexes
for the table with the new filegroup specified.
Since some of the non-clustered indexes may be located in
a different filegroup, they are moved to the table's filegroup.
See index (re-)creation script description below.
- Those scripts are executed within a transaction one-by-one.
Index (re-)creation script structure:
1) compose drop index commands list create index commands list
after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup.
2) compose create index (with drop existing) commands for the clustered
indexes that are to be moved. For tables without any clustered
index the PK supporting index is made clustered and recreated with other.
3) compose create index commands list after existing indexes
for non-clustered indexes which are located not in the desired
filegroup or if the clustered index for the table is not in the
desired filegroup. The index creation command does not include
filegroup name, so it will be created in the same filegroup
as the table it belongs to.
Those commands are saved in a table with following structure:
- record id (identity)
- table name
- sequence number (the same as topics listed just above)
- index name
- sql command
The very structure manipulation script will go through that table
in order of table name then sequence number then index name
(latter is just for convinience - to analyze the log).
There will be a transaction for each table - too costly to make
it around everything.
Script comments verbosely its execution process at the run time.
Script is also is assumed to be a tool for operations team
parameterized by the table with tables/filegroups
(see statements inserting data into #desired_layout table variable).
In order to get all the messages in a printable form the script
output must be directed to text or a file.
**
** Input Parameters: database/table/filegroup mapping in a form
of insert statements (see configurable part below).
** Returns: a table with database/table/index names with filegroup names
for each object: original one, configured(desired) one and the final one,
status of the operation for each index is specified.
** Modification History
** Sr Date Modified By Reason
** 1 05/01/2007 Alexander Karmanov Initial version
*******************************************************************************/
use master
go
set nocount on
declare
@script_name sysname
select
@script_name = 'Database tables moved to proper filegroups.sql'
print replicate('-', 80)
print 'Script name: ' @script_name
print 'Server: ' @@servername
print 'Database: ' db_name()
print 'User: ' suser_sname()
print 'Start time: ' cast(getdate() as varchar)
print replicate('-', 80)
go
/******************************************************************************
Here is a configurable part:
mapping between tables and filegroups
*******************************************************************************/
-- declare a table with desired database tables layout
if object_id('tempdb..#desired_layout') is not null drop table #desired_layout
create table #desired_layout (database_name sysname, table_name sysname, filegroup_name sysname)
insert into #desired_layout (database_name, table_name, filegroup_name)
-- database_name_1
/* database_name_1 is not moved for a while */
-- database_name_2
select 'database_name_2', 'address_type', 'srv001_db_2_group_1' union all
<....>
select 'database_name_2', 'workflow_status', 'srv001_db_2_group_x'
/******************************************************************************
End of configurable part,
below goes the actual code
*******************************************************************************/
go
use tempdb
go
-- a table with the column list for all the indexes in the source database is prepared,
-- but is left emtpy - it's needed to be created prior to a helper UDF
if object_id('tempdb..index_columns') is not null drop table index_columns
create table index_columns (database_name sysname, table_name sysname, index_name sysname,
column_name sysname, key_no int,
is_index_clustered bit, is_index_unique bit, is_column_descending bit)
go
-- a helper function converting a table with column names into a comma-separated list
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
go
create function dbo.f_get_column_list (
@database_name sysname,
@table_name sysname,
@index_name sysname
)
returns varchar(8000)
as
begin
declare @col_list varchar(8000)
select @col_list = isnull(@col_list ', ', '')
column_name ' '
case is_column_descending when 1 then 'DESC' else '' end
from index_columns
where database_name = @database_name
and table_name = @table_name
and index_name = @index_name
order by key_no
return @col_list
end
go
-- after that point the only 'go' command is the very final one - many variables are reused.
declare
@sql varchar(8000),
@crlf char(2),
@rowcnt int
select
@crlf = char(13) char(10),
@sql = ''
-- check for existence of all the filegroups mentioned in the layout table, exit if there is any problem:
-- get a list of existing filegroups for all the databases:
if object_id('tempdb..#existing_filegroups') is not null drop table #existing_filegroups
create table #existing_filegroups (database_name sysname, filegroup_name sysname)
select @sql = @sql @crlf 'select ''' database_name ''' as database_name, groupname as group_name from '
database_name '.dbo.sysfilegroups union all'
from #desired_layout
group by database_name
select
@sql = replace (@sql ';', 'union all;', '')
insert into #existing_filegroups (database_name, filegroup_name)
exec(@sql)
(select @rowcnt = count(distinct d.filegroup_name)
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null)
if @rowcnt > 0
begin
select 'In ' upper(d.database_name) ' database there is no filegroup ''' upper(d.filegroup_name) ''''
as 'Filegroups that does not exist in the target database:'
from #desired_layout d
left join #existing_filegroups e
on d.database_name = e.database_name
and d.filegroup_name = e.filegroup_name
where e.database_name is null
group by d.database_name, d.filegroup_name
print 'Script is aborted because there is at least one filegroup that does not exist (see above).'
return
end
print 'Check for the missing filegroups completed successfully - all the filegroups mentioned in the list do exist.'
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#existing_layout') is not null drop table #existing_layout
create table #existing_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf
'g2.groupname as table_filegroup_name ' @crlf
' from ' database_name '.dbo.sysobjects o ' @crlf
' join ' database_name '.dbo.sysindexes i ' @crlf
' on o.id = i.id ' @crlf
' join ' database_name '.dbo.sysfilegroups g ' @crlf
' on i.groupid = g.groupid ' @crlf
' left join (select c.id, o2.name ' @crlf
' from ' database_name '.dbo.sysconstraints c ' @crlf
' join ' database_name '.dbo.sysobjects o2 ' @crlf
' on c.constid = o2.id ' @crlf
' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf
' on o.id = k.id ' @crlf
' join ' database_name '.dbo.sysindexes i2 ' @crlf
' on o.id = i2.id ' @crlf
' and i2.indid < 2 ' @crlf
' join ' database_name '.dbo.sysfilegroups g2 ' @crlf
' on i2.groupid = g2.groupid ' @crlf
' where o.xtype = ''U'' ' @crlf
' and i.name not like ''\_WA%'' escape ''\'' ' @crlf
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf
' and i.indid < 255 ' @crlf
from #desired_layout
group by database_name
insert into #existing_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- compose table with commands changing the database structure
if object_id('tempdb..#sql') is not null drop table #sql
create table #sql (rowid int identity (1,1), database_name sysname, table_name sysname,
index_name sysname, seq_number int, sql varchar(7000))
-- get all the indexes that must be dropped:
-- 1. indexes that are not clustered, are not created as PK or UNIQUE constraint.
-- 2. either:
-- 2a. index is located not in a desired filegroup
-- 2b. clustered index on the table (index belongs to) is located not in a desired filegroup
-- All those indexes got sequence code of 1 meaning it will be executed first.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 1 as seq_number,
sql = 'use ' e.database_name '; drop index dbo.' e.table_name '.' e.index_name
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.is_pk_or_uk = 0
and e.ind_id > 1
and (
e.filegroup_name <> d.filegroup_name
or
e.table_filegroup_name <> d.filegroup_name
)
-- get all the indexes that must be created as clustered with drop_existing option:
-- 1. all the indexes that are clustered or PK or unique
-- (all the PK indexes on the tables that don't have clustered key get here as PK/UQ so the distinction needs to be done -
-- when the CLUSTERED word is included.)
-- All those indexes got sequence code of 2 meaning they will be executed when all the dependant indexes are dropped
-- The very command is to be added later - now just identifiers.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct e.database_name, e.table_name, e.index_name, 2 as seq_number,
sql = 'use ' e.database_name '; create %unique% %clustered% index ' e.index_name ' on dbo.' e.table_name
'(%column_list%) with fillfactor = 90, drop_existing on ' quotename(d.filegroup_name)
from #existing_layout e
join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where
e.is_pk_or_uk = 1 -- check for PK or unique key
or e.ind_id = 1 -- indid in sysindexes means clustered key on table
-- get all the indexes that are dropped in the first step - with the new destination filegroup and
-- all the parameters like fillfactor, and so on.
-- no need to analyze layouts, just take those that were marked as to be dropped and
-- compose create index statement for them.
insert #sql (database_name, table_name, index_name, seq_number, sql)
select distinct s.database_name, s.table_name, s.index_name, 3 as seq_number,
sql = 'use ' s.database_name '; create %unique% nonclustered index ' s.index_name ' on dbo.' s.table_name
'(%column_list%) with fillfactor = 90 on ' quotename(d.filegroup_name)
from #sql s
join #desired_layout d
on s.database_name = d.database_name
and s.table_name = d.table_name
where seq_number = 1
-- get attributes, column lists from the existing database
select @sql = null
select @sql = isnull(@sql @crlf, '')
'use ' database_name @crlf
'insert into tempdb..index_columns (database_name, table_name, index_name, column_name, ' @crlf
' key_no, is_index_clustered, is_index_unique, is_column_descending)' @crlf
'select ''' database_name ''', o.name as table_name, i.name as index_name, c.name as column_name, k.keyno, ' @crlf
' INDEXPROPERTY (o.id, i.name, ''isclustered'') as is_index_clustered,' @crlf
' INDEXPROPERTY (o.id, i.name, ''isunique'') as is_index_unique,' @crlf
' INDEXKEY_PROPERTY(o.id, i.indid, k.keyno, ''isdescending'') as is_column_descending' @crlf
' from ' database_name '.dbo.sysobjects o' @crlf
' join ' database_name '.dbo.sysindexes i' @crlf
' on o.id = i.id' @crlf
' join ' database_name '.dbo.sysindexkeys k' @crlf
' on i.id = k.id' @crlf
' and i.indid = k.indid' @crlf
' join ' database_name '.dbo.syscolumns c' @crlf
' on k.colid = c.colid' @crlf
' and k.id = c.id' @crlf
' where ' @crlf
' o.xtype = ''U''' @crlf
' and INDEXPROPERTY (i.id, i.name, ''isstatistics'') = 0' @crlf
''
from #desired_layout
group by database_name
exec(@sql)
-- replace %column_list% placeholders in the index creation statements with proper column list
update #sql
set sql = replace(sql, '%column_list%', dbo.f_get_column_list(database_name, table_name, index_name))
-- specify proper clustered option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_clustered = 1
-- for indexes that are PK in table without any clustered index - we make PK indexes clustered,
-- so it will be rebuilt in the new (desired) filegroup.
update s
set sql = replace(sql, '%clustered%', 'clustered')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
join #existing_layout e
on s.database_name = e.database_name
and s.table_name = e.database_name
and e.ind_id = 0 -- that means the table is a heap right now (no clustered index)
-- for the rest set it as nonclustered
update s
set sql = replace(sql, '%clustered%', 'nonclustered')
from #sql s
-- specify proper unique option for indexes:
-- for indexes that are clustered already:
update s
set sql = replace(sql, '%unique%', 'unique')
from #sql s
join tempdb..index_columns ic
on s.database_name = ic.database_name
and s.table_name = ic.table_name
and s.index_name = ic.index_name
and ic.is_index_unique = 1
-- for the rest set it as non-unique
update s
set sql = replace(sql, '%unique%', '')
from #sql s
-- at this point all the commands are prepared in #sql table
-- and all we need is just to execute them one-by-one.
-- Actions for a table are wrapped into a transaction,
-- so if there is any error no table indexes structures
print cast(getdate() as varchar) ': Starting processing the tables'
declare
@rowid int,
@database_name sysname,
@table_name sysname,
@index_name sysname,
@seq_number int
declare t cursor static for
select distinct database_name, table_name
from #sql
order by database_name, table_name
open t
fetch next from t into @database_name, @table_name
while @@fetch_status = 0
begin
print cast(getdate() as varchar) ': Processing table ' @table_name ' in ' @database_name ' database'
begin tran
declare i cursor static for
select rowid, index_name, sql, seq_number
from #sql
where database_name = @database_name
and table_name = @table_name
order by seq_number, index_name
open i
fetch next from i into @rowid, @index_name, @sql, @seq_number
while @@fetch_status = 0
begin
if @seq_number = 1 print cast(getdate() as varchar) ': Dropping index ' @table_name '.' @index_name
else if @seq_number = 2 print cast(getdate() as varchar) ': Moving index ' @table_name '.' @index_name ' to another filegroup'
else if @seq_number = 3 print cast(getdate() as varchar) ': Re-creating index ' @table_name '.' @index_name
print cast(getdate() as varchar) ': A command to be executed: ' @sql
exec(@sql)
-- if there is an error - stop processing that table, rolling back transaction for it and reporting failure for it
if @@error <> 0
begin
rollback
print cast(getdate() as varchar) ': Command failed to execute'
break
end
print cast(getdate() as varchar) ': Command successfully completed'
fetch next from i into @rowid, @index_name, @sql, @seq_number
end
close i
deallocate i
-- if we have uncommitted transaction here that means no error occured in th loop on the indexes
-- and there is no transaction rollback issued above - i.e. everything is fine for that table,
-- report success on it (and commit it of course):
if @@trancount > 0
begin
commit
print cast(getdate() as varchar) ': Successfully processed table ' @table_name ' in ' @database_name ' database'
end
fetch next from t into @database_name, @table_name
end -- of loop on t cursor
close t
deallocate t
-- get the tables layout after the changes (similar to what was done before the changes:
-- get the existing database/table/index/filegroup layout
if object_id('tempdb..#updated_layout') is not null drop table #updated_layout
create table #updated_layout (database_name sysname, table_name sysname, index_name sysname, ind_id int,
filegroup_name sysname, is_pk_or_uk int, table_filegroup_name sysname)
select @sql = null
select @sql = isnull(@sql @crlf 'union all' @crlf, '') 'select ''' database_name ''' as database_name, ' @crlf
'o.name as table_name, i.name as index_name, i.indid as ind_id, g.groupname as filegroup_name, ' @crlf
'case when k.name = i.name then 1 else 0 end as is_pk_or_uk, ' @crlf
'g2.groupname as table_filegroup_name ' @crlf
' from ' database_name '.dbo.sysobjects o ' @crlf
' join ' database_name '.dbo.sysindexes i ' @crlf
' on o.id = i.id ' @crlf
' join ' database_name '.dbo.sysfilegroups g ' @crlf
' on i.groupid = g.groupid ' @crlf
' left join (select c.id, o2.name ' @crlf
' from ' database_name '.dbo.sysconstraints c ' @crlf
' join ' database_name '.dbo.sysobjects o2 ' @crlf
' on c.constid = o2.id ' @crlf
' where o2.xtype in (''PK'', ''UQ'')) k ' @crlf
' on o.id = k.id ' @crlf
' join ' database_name '.dbo.sysindexes i2 ' @crlf
' on o.id = i2.id ' @crlf
' and i2.indid < 2 ' @crlf
' join ' database_name '.dbo.sysfilegroups g2 ' @crlf
' on i2.groupid = g2.groupid ' @crlf
' where o.xtype = ''U'' ' @crlf
' and i.name not like ''\_WA%'' escape ''\'' ' @crlf
' --and indexproperty(object_id(o.name), i.name, ''isstatistics'') = 0 ' @crlf
' and i.indid < 255 ' @crlf
from #desired_layout
group by database_name
insert into #updated_layout (database_name, table_name, index_name, ind_id, filegroup_name, is_pk_or_uk, table_filegroup_name)
exec(@sql)
-- report difference between the previously existing layout, what was the intent and what we've got after the update:
print 'Script execution status report (see the grid if it is not displayed below):'
select
e.database_name '.' e.table_name '.' e.index_name as object,
e.filegroup_name as original_fileroup,
isnull(d.filegroup_name, 'NOT CONFIGURED') as desired_new_filegroup,
isnull(u.filegroup_name, 'OBJECT DISAPPEARED!!!') as actual_new_filegroup,
case
when d.filegroup_name is null then 'NOT CONFIGURED'
when u.filegroup_name is null then 'OBJECT WAS LOST'
when d.filegroup_name <> u.filegroup_name then 'NO UPDATE HAPPENED'
else 'ok'
end as update_status
from #existing_layout e
left join #updated_layout u
on e.database_name = u.database_name
and e.table_name = u.table_name
and e.index_name = u.index_name
left join #desired_layout d
on e.database_name = d.database_name
and e.table_name = d.table_name
where e.ind_id > 0 -- no need to show items for the heap (which item is not actually an index anyway).
-- If such heap table has a PK it is shown in a separate line (with ind_id > 1)
order by e.database_name, e.table_name, e.index_name
go
-- clean-up
use tempdb
if object_id('tempdb..f_get_column_list') is not null drop function f_get_column_list
if object_id('tempdb..index_columns') is not null drop table index_columns
go
-- to release any database we switched to
use master
go
-- the end of the script
/*
select *
from #desired_layout
select *
from #updated_layout
order by table_name
select *
from #existing_layout
order by table_name
select *
from #sql
order by table_name
*/
The Best Script ever on -- SHRINK LOG FILE
http://education.sqlfarms.com/education/ShowPost.aspx?PostID=117
-----------------------------------------------------------------
SQL Farms Admin :
================
Now, to help you resolve the shrink problem:
What you describe happens often if the database was created with small disk space allocation and was set to autogrow, and several autogrows indeed took place. The result of 2, instead of 0, in the DBCC LOGINFO command prevents the server from shrinking the file appropriately. This needs to be overcome by backing up portions of the transaction log, (until transaction log fully backed up) and then truncating the transaction log.
To help you in this task, I am attaching the following script, which should get you the desired result. (I had the script around for a while, and it worked for me, but although I tuned it a little, I am not sure who the original developer is, to give him creadit :) ).
Please let me know whether this helped your problem.
/**************************************************************
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
*************************************************************/
-- DB parameters
DECLARE @target_percent tinyint
DECLARE @target_size_MB int
DECLARE @max_iterations int
DECLARE @backup_log_opt nvarchar(1000)
SET @target_percent = 0
SET @target_size_MB = 0
SET @max_iterations = 1000
SET @backup_log_opt = 'WITH TRUNCATE_ONLY'
DECLARE @db SYSNAME
DECLARE @last_row INT
DECLARE @log_size DECIMAL(15,2)
DECLARE @unused1 DECIMAL(15,2)
DECLARE @unused DECIMAL(15,2)
DECLARE @shrinkable DECIMAL(15,2)
DECLARE @iteration INT
DECLARE @file_max INT
DECLARE @file INT
DECLARE @fileid VARCHAR(5)
DECLARE @prev_max_iterations INT
DECLARE @command VARCHAR(500)
SET NOCOUNT ON
SET @db = db_name()
SET @iteration = 0
SET @prev_max_iterations = 2^31-1
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo
-- This table stores the results of DBCC LOGINFO
CREATE TABLE #loginfo(
id INT identity,
FileId INT,
FileSize NUMERIC(22,0),
StartOffset NUMERIC(22,0),
FSeqNo INT,
Status INT,
Parity SMALLINT,
TimeorLSN VARCHAR(25))
CREATE UNIQUE CLUSTERED INDEX loginfo_FSeqNo ON #loginfo ( FSeqNo, StartOffset )
IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
CREATE TABLE #logfiles (
id INT IDENTITY(1,1),
fileid VARCHAR(5) NOT NULL)
INSERT INTO #logfiles ( fileid )
SELECT CONVERT(VARCHAR, fileid )
FROM sysfiles
WHERE status & 0x40 = 0x40
SET @file_max = @@ROWCOUNT
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'dbcc loginfo' )
SET @last_row = @@rowcount
PRINT 'Max iterations = ' + rtrim(cast(@max_iterations as varchar(20)))
SELECT @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
FROM #loginfo
SET @unused1 = @unused -- save for later
-- changed this so that it will print with rest of output SBP
PRINT '
iteration ........... = ' + cast(@iteration as varchar(10)) + '
log size, MB ........ = ' + cast(@log_size as varchar(10)) + '
unused log, MB ...... = ' + cast(@unused as varchar(10)) + '
shrinkable log, MB .. = ' + cast(@shrinkable as varchar(10)) + '
shrinkable % ........ = ' + cast(convert( DECIMAL(6,2), @shrinkable * 100 / @log_size ) as varchar(10))
-- If @target_size_MB is entered, it will take precedence
-- over @target_percent, by calculating a new @target_percent.
IF @target_size_MB > 0
SET @target_percent = (@target_size_MB / @log_size) * 100
ELSE
SET @target_size_MB = 10
-- Changed @target_percent to + 1, because many times the end result is
-- slightly larger than the target.
WHILE @shrinkable * 100 / @log_size > (@target_percent + 1)
AND @iteration < @max_iterations
BEGIN
SET @iteration = @iteration + 1 -- this is just a precaution
SET @file = 0
WHILE @file < @file_max
BEGIN
SET @file = @file + 1
SELECT @fileid = fileid
FROM #logfiles
WHERE [id] = @file
SET @command = 'DBCC SHRINKFILE( ' + @fileid + ',' + RTRIM(CAST(@target_size_MB as varchar(10))) + ')'
PRINT @command
EXEC (@command)
END
EXEC( 'BACKUP LOG [' + @db + '] ' + @backup_log_opt )
TRUNCATE TABLE #loginfo
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'DBCC LOGINFO' )
SET @last_row = @@ROWCOUNT
-- The most iterations we really need to do is the number of logical log files,
-- and this should change if the log is shrinking. Therefore, reset
-- @max_iterations within the loop.
SELECT @max_iterations = COUNT(*)
FROM #loginfo
-- If the number of logical log files did not change from last iteration, get out.
IF @max_iterations = @prev_max_iterations
SET @max_iterations = 0
ELSE
SET @prev_max_iterations = @max_iterations
PRINT 'Max iterations = ' + RTRIM(CAST(@max_iterations as varchar(20)))
SELECT @log_size = SUM( FileSize ) / 1048576.00,
@unused = SUM( CASE WHEN Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00,
@shrinkable = SUM( CASE WHEN id < @last_row - 1 and Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00
FROM #loginfo
PRINT '
iteration ........... = ' + CAST(@iteration AS VARCHAR(10)) + '
log size, MB ........ = ' + CAST(@log_size AS VARCHAR(10)) + '
unused log, MB ...... = ' + CAST(@unused AS VARCHAR(10)) + '
shrinkable log, MB .. = ' + CAST(@shrinkable AS VARCHAR(10)) + '
shrinkable % ........ = ' + CAST(CONVERT( DECIMAL(6,2), @shrinkable * 100 / @log_size ) AS VARCHAR(10))
END
IF @unused1 < @unused
SELECT 'After ' + CONVERT( VARCHAR, @iteration ) +
' iterations the unused portion of the log has grown from ' +
CONVERT( VARCHAR, @unused1 ) + ' MB to ' +
CONVERT( VARCHAR, @unused ) + ' MB.'
UNION ALL
SELECT 'Since the remaining unused portion is larger than 10 MB,' WHERE @unused > 10
UNION ALL
SELECT 'you may try running this procedure again with a higher number of iterations.' WHERE @unused > 10
UNION ALL
SELECT 'Sometimes the log would not shrink to a size smaller than several Megabytes.' WHERE @unused <= 10
else
select 'It took ' + convert( VARCHAR, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( VARCHAR, @unused1 ) + ' MB to ' +
convert( VARCHAR, @unused ) + ' MB'
-- cleanup
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo
IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
GO
-----------------------------------------------------------------
SQL Farms Admin :
================
Now, to help you resolve the shrink problem:
What you describe happens often if the database was created with small disk space allocation and was set to autogrow, and several autogrows indeed took place. The result of 2, instead of 0, in the DBCC LOGINFO command prevents the server from shrinking the file appropriately. This needs to be overcome by backing up portions of the transaction log, (until transaction log fully backed up) and then truncating the transaction log.
To help you in this task, I am attaching the following script, which should get you the desired result. (I had the script around for a while, and it worked for me, but although I tuned it a little, I am not sure who the original developer is, to give him creadit :) ).
Please let me know whether this helped your problem.
/**************************************************************
Input Params:
-------------
@target_percent tinyint. default = 0. Target percentage of remaining shrinkable
space. Defaults to max possible.
@target_size_MB int. default = 10. Target size of final log in MB.
@max_iterations int. default = 1000. Number of loops (max) to run proc through.
@backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.
*************************************************************/
-- DB parameters
DECLARE @target_percent tinyint
DECLARE @target_size_MB int
DECLARE @max_iterations int
DECLARE @backup_log_opt nvarchar(1000)
SET @target_percent = 0
SET @target_size_MB = 0
SET @max_iterations = 1000
SET @backup_log_opt = 'WITH TRUNCATE_ONLY'
DECLARE @db SYSNAME
DECLARE @last_row INT
DECLARE @log_size DECIMAL(15,2)
DECLARE @unused1 DECIMAL(15,2)
DECLARE @unused DECIMAL(15,2)
DECLARE @shrinkable DECIMAL(15,2)
DECLARE @iteration INT
DECLARE @file_max INT
DECLARE @file INT
DECLARE @fileid VARCHAR(5)
DECLARE @prev_max_iterations INT
DECLARE @command VARCHAR(500)
SET NOCOUNT ON
SET @db = db_name()
SET @iteration = 0
SET @prev_max_iterations = 2^31-1
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo
-- This table stores the results of DBCC LOGINFO
CREATE TABLE #loginfo(
id INT identity,
FileId INT,
FileSize NUMERIC(22,0),
StartOffset NUMERIC(22,0),
FSeqNo INT,
Status INT,
Parity SMALLINT,
TimeorLSN VARCHAR(25))
CREATE UNIQUE CLUSTERED INDEX loginfo_FSeqNo ON #loginfo ( FSeqNo, StartOffset )
IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
CREATE TABLE #logfiles (
id INT IDENTITY(1,1),
fileid VARCHAR(5) NOT NULL)
INSERT INTO #logfiles ( fileid )
SELECT CONVERT(VARCHAR, fileid )
FROM sysfiles
WHERE status & 0x40 = 0x40
SET @file_max = @@ROWCOUNT
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'dbcc loginfo' )
SET @last_row = @@rowcount
PRINT 'Max iterations = ' + rtrim(cast(@max_iterations as varchar(20)))
SELECT @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
FROM #loginfo
SET @unused1 = @unused -- save for later
-- changed this so that it will print with rest of output SBP
PRINT '
iteration ........... = ' + cast(@iteration as varchar(10)) + '
log size, MB ........ = ' + cast(@log_size as varchar(10)) + '
unused log, MB ...... = ' + cast(@unused as varchar(10)) + '
shrinkable log, MB .. = ' + cast(@shrinkable as varchar(10)) + '
shrinkable % ........ = ' + cast(convert( DECIMAL(6,2), @shrinkable * 100 / @log_size ) as varchar(10))
-- If @target_size_MB is entered, it will take precedence
-- over @target_percent, by calculating a new @target_percent.
IF @target_size_MB > 0
SET @target_percent = (@target_size_MB / @log_size) * 100
ELSE
SET @target_size_MB = 10
-- Changed @target_percent to + 1, because many times the end result is
-- slightly larger than the target.
WHILE @shrinkable * 100 / @log_size > (@target_percent + 1)
AND @iteration < @max_iterations
BEGIN
SET @iteration = @iteration + 1 -- this is just a precaution
SET @file = 0
WHILE @file < @file_max
BEGIN
SET @file = @file + 1
SELECT @fileid = fileid
FROM #logfiles
WHERE [id] = @file
SET @command = 'DBCC SHRINKFILE( ' + @fileid + ',' + RTRIM(CAST(@target_size_MB as varchar(10))) + ')'
PRINT @command
EXEC (@command)
END
EXEC( 'BACKUP LOG [' + @db + '] ' + @backup_log_opt )
TRUNCATE TABLE #loginfo
INSERT INTO #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, TimeorLSN )
EXEC ( 'DBCC LOGINFO' )
SET @last_row = @@ROWCOUNT
-- The most iterations we really need to do is the number of logical log files,
-- and this should change if the log is shrinking. Therefore, reset
-- @max_iterations within the loop.
SELECT @max_iterations = COUNT(*)
FROM #loginfo
-- If the number of logical log files did not change from last iteration, get out.
IF @max_iterations = @prev_max_iterations
SET @max_iterations = 0
ELSE
SET @prev_max_iterations = @max_iterations
PRINT 'Max iterations = ' + RTRIM(CAST(@max_iterations as varchar(20)))
SELECT @log_size = SUM( FileSize ) / 1048576.00,
@unused = SUM( CASE WHEN Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00,
@shrinkable = SUM( CASE WHEN id < @last_row - 1 and Status = 0 THEN FileSize ELSE 0 END ) / 1048576.00
FROM #loginfo
PRINT '
iteration ........... = ' + CAST(@iteration AS VARCHAR(10)) + '
log size, MB ........ = ' + CAST(@log_size AS VARCHAR(10)) + '
unused log, MB ...... = ' + CAST(@unused AS VARCHAR(10)) + '
shrinkable log, MB .. = ' + CAST(@shrinkable AS VARCHAR(10)) + '
shrinkable % ........ = ' + CAST(CONVERT( DECIMAL(6,2), @shrinkable * 100 / @log_size ) AS VARCHAR(10))
END
IF @unused1 < @unused
SELECT 'After ' + CONVERT( VARCHAR, @iteration ) +
' iterations the unused portion of the log has grown from ' +
CONVERT( VARCHAR, @unused1 ) + ' MB to ' +
CONVERT( VARCHAR, @unused ) + ' MB.'
UNION ALL
SELECT 'Since the remaining unused portion is larger than 10 MB,' WHERE @unused > 10
UNION ALL
SELECT 'you may try running this procedure again with a higher number of iterations.' WHERE @unused > 10
UNION ALL
SELECT 'Sometimes the log would not shrink to a size smaller than several Megabytes.' WHERE @unused <= 10
else
select 'It took ' + convert( VARCHAR, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( VARCHAR, @unused1 ) + ' MB to ' +
convert( VARCHAR, @unused ) + ' MB'
-- cleanup
IF OBJECT_ID('tempdb..#loginfo', 'U') IS NOT NULL
DROP TABLE #loginfo
IF OBJECT_ID('tempdb..#logfiles', 'U') IS NOT NULL
DROP TABLE #logfiles
GO
Abonneren op:
Posts (Atom)