Copy SQL Server database tables

Ever needed to copy an SQL Server database, but get only its data and leave out everything else?
Here's a T-SQL script that you can use to copy all the database's tables' data, and leave out everything else (indexes, partitions, programmability components, etc...)
The script will go through all tables in the 'dbo' schema of the source database (named 'sourceDatabase' in this example), create a copy of the table in the 'dbo' schema of the destination database (named 'targetDatabase') and insert all data from the source table in the destination table (by using a SELECT INTO).

Here's the code:

DECLARE @tableName varchar(300)
DECLARE @sqlStmt nvarchar(300)

DECLARE curTables CURSOR LOCAL FOR
SELECT table_name FROM sourceDatabase.INFORMATION_SCHEMA.tables


OPEN curTables

FETCH NEXT FROM curTables INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlStmt = 'SELECT * INTO targetDatabase.dbo.' + @tableName + ' FROM sourceDatabase.dbo.' + @tableName
EXEC sp_executesql @sqlStmt

FETCH NEXT FROM curTables INTO @tableName
END

CLOSE curTables
DEALLOCATE curTables

Describe a table in SQL Server 2005

Here's a quick snippet of SQL that allows you to describe a table in a SQL Server 2005 database, similar to the DESC or DESCRIBE command in Oracle and MySQL:

SELECT column_name, data_type,column_default, is_nullable, character_maximum_lenght, numeric_precision, datetime_precision
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='MyTableName'

Detailed info on INFORMATION_SCHEMA: http://msdn.microsoft.com/en-us/library/ms186778.aspx

How to reset SQL Server identity column

Generally, to reset an identity column to value n on table XYZ:
DBCC CHECKIDENT('XYZ',RESEED,n)

So, to reset an identity column to value 17 on table XYZ:
DBCC CHECKIDENT('XYZ',RESEED,17)

Excel says 'user does not have permissions to run DBCC Traceon'

This is due to an incompatibility between Excel 2000 and SQL Server 2005. As a workaround, try this: press the Options button and replace the text on the "Application Name" textbox on the "SQL Server Login" panel with a single white space

Fixing orphan logins on SQL Server 2005

Ever restored a backup that didn't include database logins, winding up with a lot of orphan logins? Here's a way out:

To set "targetUserNewLogin" as the new login for user "targetUser", run the following stored procedure (you can leave the first parameter unchanged):
sp_change_users_login 'update_one', 'targetUser','targetUserNewLogin'

If you need to get the list of users without logins:
SELECT * FROM sysusers
WHERE issqluser = 1 AND (sid IS NOT NULL AND sid <> 0x0)
AND suser_sname(sid) IS NULL


If you need to delete schema ownership:

SELECT s.* FROM sys.schemas AS s
INNER JOIN sys.database_principals AS dp ON dp.principal_id = s.principal_id
WHERE dp.[name] = 'someUser';

SELECT s.[name] as schemaname, o.[name] AS objectname, o.type_desc
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
INNER JOIN sys.database_principals AS dp ON dp.principal_id =s.principal_id
WHERE dp.[name] = 'someUser'
ORDER BY s.[name], o.type_desc, o.[name];

DROP SCHEMA someUser;

Multiple-step OLE DB operation generated errors

When running an Execute SQL Task on SSIS 2005 you get the following error:

Error: Executing the query "your SQL code here" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The last time I ran into this error it turned out that I had (yes, it was copy/paste ;D) some comments in the middle of the T-SQL code - deleting the comments got it working, hope this method helps you.