How do you know if your database exists with MSSQL?
- When creating or programming a database installs, the first thing to do is to verify if this is a brand new install or an upgrade.
- If the database already exists, then it's an upgrade.
- If the database does not exists, then it's a brand new install.
- SQL Server 2000 keeps tracks of all existing databases with the table sysdatabases in the master database.
- SQL Server 2005 and SQL Server 2008 keep tracks of all existing databases with the table sys.sysdatabases in the master database.
- It's only a matter of querying the sys.sysdatabases or the sysdatabases to know if the database already exists.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Query of sys.sysdatabases
- USE master;
- go
- SELECT * FROM sys.databases WHERE name = 'sql911';
- go
name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ ------------ ---------------- ----------------- ----- ------------------------------------------------------------ ------------- ------------------- ------------------------------- ------------------------ ------------------------------------------------------------ ----------------------------- -------------- ------------------------------------------------------------ ------------------ ------------------------------------------------------------ ----------------------- ----------------------- ----------------------------- ----------------------- ---------------- ------------------ ------------------- ---------------- ----------------------------- ------------------------ ----------------------- ------------------------ ---------------------------- ----------------------- ------------------- ----------------- ----------------- -------------------------- --------------------------------- ------------ ------------- ------------------ -------------- ------------------- ------------------------------------ ----------------- -------------- ------------------------------------------------------------ ---------------------- sql911 10 NULL 0x01050000000000051500000059843EB8EA39F604BD3BFDE9EB030000 2007-10-22 09:30:51.020 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 1 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 147D09F5-220C-4A22-94B3-937F2AC73317 0 0 NOTHING 0 (1 row(s) affected)
Improved query of sys.sysdatabases
- SELECT CAST(name AS VARCHAR(20)) AS db, database_id, create_date,compatibility_level,collation_name,is_read_only, is_auto_shrink_on,is_cleanly_shutdown
- FROM sys.databases WHERE name = 'sql911';
- SELECT @@ROWCOUNT AS 'Last query rows';
- go
db database_id create_date compatibility_level collation_name is_read_only is_auto_shrink_on is_cleanly_shutdown -------------------- ----------- ----------------------- ------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ----------------- ------------------- sql911 10 2007-10-22 09:30:51.020 90 SQL_Latin1_General_CP1_CI_AS 0 1 0 (1 row(s) affected) Last query rows --------------- 1
- Line 4: @@rowcount returns the number of rows that were returned by the last query.
- Ifg you are still using SQL Server 2000, the table to use is: sysdatabases instead of sys.sysdatabases