Sune Rievers blog

I’m currently replicating data from MSSQL2005 to MSSQL2000, which has proven a bit of a PITA. I succeeded using transactional replication, but afterwards I needed to verify all data has been copied. Tables without primary key (stupid, I know) has not been replicated, and some views referencing missing tables or databases are not either (probably shouldn’t anyway).

However, it’s pretty damn useful to just get a list of tables with count of rows, which led me to this blog post.

The SQL code needed is this:

USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

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

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