Sune Rievers blog

Found this today: http://vyaskn.tripod.com/code.htm#inserts

Narayana Vyas Kondreddi has created a stored procedure, that dumps all data from a MS-SQL database. It works like a charm! I think this is one of the few features where MySQL is actually ahead of MS-SQL, via the mysqldump tool. This makes backup and server transitions a breeze, when dealing with MySQL, and now also MS-SQL (sort of). Only inserts are supported, and there is no checking for existing data.

The script is really easy to use, after creating the SP, you run this sql query: EXEC sp_generate_inserts 'titles' to dump all data from the ‘titles’ table. It also allows some customization, and reducing the amount of exported data, eg using sql where-statements.

To export all data from all tables in the database, run:

SELECT 'EXEC sp_generate_inserts ' +
'[' + name + ']' +
',@owner = ' +
'[' + RTRIM(USER_NAME(uid)) + '],' +
'@ommit_images = 1, @disable_constraints = 1'
FROM sysobjects
WHERE type = 'U' AND
OBJECTPROPERTY(id,'ismsshipped') = 0

The entire script is located here for MS-SQL 2005.

You would probably also need to script the db_owner user (when login is already created on the server):

CREATE USER [mydatabase] FOR LOGIN [myuser] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember 'db_owner', 'myuser'
GO

Today I had to deal with a child-parent table with a list of user groups. These groups are nested, and persons in a subgroup should have access to all information that users of parent groups have.

I tried to do this by using a function or stored procedure, but none of these were optimal solutions. In the end, I stumbled across the WITH statement in T-SQL.

From this blog and this, I learned that it is actually pretty easy to extract the list of children, given a parent id (or vice versa).

USE tempdb;
GO

DECLARE @t TABLE (
Code INT NOT NULL UNIQUE CLUSTERED,
[Name] VARCHAR(25),
Parent INT NULL
);

INSERT INTO @t VALUES(1,      'Name1',   NULL);
INSERT INTO @t VALUES(2,      'Name2',   1);
INSERT INTO @t VALUES(3,      'Name3',   NULL);
INSERT INTO @t VALUES(4,      'Name4',   2);
INSERT INTO @t VALUES(5,      'Name5',   3);

WITH hierarchy
AS
(
SELECT Code, Parent, [Name], 0 AS lvl, CAST('\' + LTRIM(Code) + '\' AS VARCHAR(MAX)) AS [path]
FROM @t
WHERE Parent IS NULL

UNION ALL

SELECT c.Code, c.Parent, c.[Name], p.lvl + 1 AS lvl, p.[path] + CAST(LTRIM(c.Code) + '\' AS VARCHAR(MAX)) AS [path]
FROM @t AS c INNER JOIN hierarchy AS p ON c.Parent = p.Code
)
SELECT (REPLICATE(SPACE(1), lvl * 4)) + [Name]
FROM hierarchy
ORDER BY [path];
GO

Above code is from http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/865bcee0-dbe0-4a4e-98ab-82ae6c802df9

This is actually pretty sweet! With proper indexes and database structure, this runs almost as fast as when joining on the top level (root parent id)!

The code for the view ended up like this:

CREATE view [dbo].[GroupLevels]

as

WITH hierarchy
AS
(
SELECT departmentId, parentDepId, departmentDesc
FROM UserGroups
WHERE parentDepId IS NULL

UNION ALL

SELECT c.departmentId, c.parentDepId, c.departmentDesc
FROM UserGroups AS c INNER JOIN hierarchy AS p ON c.parentDepId = p.departmentId
)
SELECT departmentId, parentDepId
FROM hierarchy

Now I can use this simple join statement in order to join on sub groups as well (GroupLevels is my view):

SELECT * FROM
ACCESS A,
USERS U
INNER JOIN Membership M ON M.Group IN
   (SELECT departmentId FROM GroupLevels g
        WHERE g.parentDepId = A.intGroupID)
AND M.account = U.account
AND A.level = 1

I recently had to task of changing all tables on a MS-SQL database to a new owner (well, dbo), and then I stumbled across this blog post, quoted here:

Normally you use sp_changeobjectowner(SQL Server 2000 SP3)

Sample:
Remenber to have the right permissions. In this sample I’m running as dbo and i have logged by using sa

use Northwind
EXECUTE sp_changeobjectowner 'EmployeeTerritory', 'guest'

To take back this, the parameters change a little:

use Northwind
EXECUTE sp_changeobjectowner 'guest.EmployeeTerritory', 'dbo'

Well you must do it for all tables, but using an undocumented Stored Procedure called sp_MSforeachtable you can do this in one line of code:


use Northwind
sp_MSforeachtable @command1="sp_changeobjectowner '?', 'dbo'"

Another undocumented Stored Procedure is sp_MSforeachdb which could be used for change the owner of all Databases

All views and stored procedures must however still be changed manually, but this is far easier, as they don’t contain unscriptable data (why oh why, MS-SQL, don’t you have a dump feature like MySQL has had for years??).

Grats and thanks to Jose Blanco Muradas for finding this