Before a filegroup can be removed all files associated with it need to be deleted.
Before a file can be deleted all objects associated with it need to be removed.
If you have multiple files on a filegroup and you just want to get rid of one you can run this command.
DBCC ShrinkFile(logical_file_name,EmptyFile);
This will move any objects in the file onto the other files in the filegroup. You can then delete the file.
ALTER DATABASE DatabaseName REMOVE FILE logical_file_name;
To do this you will have to make sure that here are no objects associated with the file group. This could be Tables etc, Indexes, or even Blob objects.
This bit of code will show you the objects on a file group.
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
I got this from:
http://blog.sqlauthority.com/2009/06/01/sql-server-list-all-objects-created-on-all-filegroups-in-database/
If there are objects you want to keep you will need to move them to another filegroup. To move a non-clustered index you will need to drop and recreate it on the new file group. To move a table you need to drop and recreate the clustered index.
Once the filegroup has no user objects empty you can run the same commands for removing a file as – shrinkfile and remove file.
Then you will need to run the code below to drop the filegroup:
DATABASE [databaseName] REMOVE FILEGROUP [Filegroupname];
Remember: you can not drop the Primary filegroup.
Table diff will let you compare two tables and generate a change script to sync them up.
By default it is installed here:
"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe"
In this example I am comparing two tables in the same database called Live_Support. The tables are LF29699_Orig and LF29699_New.
I have created a batch file called comapretables.cmd.
The contents of the table looks like this:
"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver sqlmgmt1 -sourcedatabase Live_Support -sourcetable LF29699_Orig -destinationserver sqlmgmt1 -destinationdatabase colt_Live_Support -destinationtable LF29699_new -f c:\LF29699_diff.sql
Execute this and you get a file c:\LF29699_diff.sql which is a change script for that table:
-- Host: sqlmgmt1
-- Database: [Live_Support]
-- Table: [dbo].[LF29699_new]
SET IDENTITY_INSERT [dbo].[LF29699_new] ON
UPDATE [dbo].[LF29699_new] SET [GroupIndex]=3 WHERE [pkid] = 3
UPDATE [dbo].[LF29699_new] SET [GroupIndex]=2 WHERE [pkid] = 683
SET IDENTITY_INSERT [dbo].[LF29699_new] OFF
You can see from the script that there are 2 differences between the tables. It does not tell you what the values in the table are currently; you do have the primary key value though so you can look it up.
The two tables you compare will need to have primary keys defined on them.
BOL tableDiff.Exe: 'http://msdn.microsoft.com/en-us/library/ms162843.aspx
I had thought that if an item came within teh scope of a cursor it would remain there until removed. However, cursors work a little like views, if you cange a value which would remove an item from a view you will no longer see it in the view. If you change the value of a data row which would put it outside the scope of teh cursor the cursor will drop that row.
This was tested on SQL 2005 sp3 – I assume it will be the same on other versions but I haven’t tested them.
I have a simple table:
CREATE TABLE [dbo].[test](
[pkid] [int] IDENTITY(1,1) NOT NULL,
[descript] [varchar](5) NOT NULL,
[value1] [int] NOT NULL,
[value2] [int] NOT NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[pkid] ASC
);
The table has 3 rows:
| Pkid | descript | value1 | value2 |
| 1 | test1 | 0 | 0 |
| 2 | test2 | 0 | 0 |
| 3 | test1 | 0 | 0 |
I then execute this script against the table:
Declare @dbName as varchar(5);
Declare @x as int;
Set @x = 1;
DECLARE dbCursor CURSOR Local Fast_Forward FOR
select descript From dbo.Test
WHERE value2 = 0;
OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
Print @dbName
Update dbo.Test
Set value2 = @x
Where descript = @dbname;
Set @x = @x+1;
FETCH NEXT FROM dbCursor INTO @dbName;
END
CLOSE dbCursor;
DEALLOCATE dbCursor;
The results in the table are:
| Pkid | descript | value1 | value2 |
| 1 | test1 | 0 | 1 |
| 2 | test2 | 0 | 2 |
| 3 | test1 | 0 | 1 |
This shows that because the value2 value of pkid 3 changed from 0 to 1 on the first update it dropped out of the cursors scope.
