Not a new database, but an empty database – no data, but it still preserves the existing schema. You have choices:
1. Use a combination of Truncate/Delete
USE MyDB
EXEC sp_msforeachtable ‘print ‘’TRUNCATE TABLE ?’’’
The command will truncate all tables in the database, EXCEPT when a column in a table has a constraint such as a foreign key. [...]
Posts Tagged ‘schema’
How to Create An Empty Database
Posted in SQL Server, tagged schema on 4 November 2009 | Leave a Comment »
Altering Table Schema and Schema Ownership – SQL Server 2005
Posted in SQL Server, tagged schema, security on 3 June 2009 | Leave a Comment »
Do you see something like this in your database?
DOMAIN\login.myTable
Username.myView
Username.someStoredProc
Sometimes these objects are created ”accidently” under user’s schema. So how do we change it back to dbo or to other schema?
ALTER SCHEMA dbo TRANSFER DOMAIN\login.myTable
This will alter the schema of myTable to dbo.
Want to dig more information for schemas in the database?
USE database_name
GO
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
You [...]
Change Default Schema In SQL Server 2005/2008
Posted in SQL Server, tagged schema, security on 15 May 2009 | Leave a Comment »
I recently found out that some schemas in my SQL2005 box are not of the default dbo (this is apart from some pre-determined schemas that we have). This seems to happen when I run sp_adduser to add a login as a user of a particular database.
CREATE LOGIN [DOMAIN\mylogin] FROM WINDOWS
GO
USE MyTestDB
GO
sp_adduser ‘DOMAIN\mylogin’
This [...]
