Tag Archives: Script

Insert data from one table to another table

2 Replies

You can insert the data from one table to another table using SELECT INTO and INSERT INTO with SELECT.. FROM clause.


— Below statement will create the temp table to insert records
select * INTO #tmpObjects from sys.sysobjects where type = ‘u’

— Below statement will create the user table to insert records.
— First will create the table and insert it details as well in new table
select * INTO tmpObjects from sys.sysobjects where type = ‘u’

–Below statement will insert new data into table
insert into tmpObjects SELECT * from sys.sysobjects where type = ‘s’

Script to list out important properties of database

1 Reply

Use below script to list out the important properties of the database

 


select
sysDB.database_id,
sysDB.Name as 'Database Name',
syslogin.Name as 'DB Owner',
sysDB.state_desc,
sysDB.recovery_model_desc,
sysDB.collation_name,
sysDB.user_access_desc,
sysDB.compatibility_level,
sysDB.is_read_only,
sysDB.is_auto_close_on,
sysDB.is_auto_shrink_on,
sysDB.is_auto_create_stats_on,
sysDB.is_auto_update_stats_on,
sysDB.is_fulltext_enabled,
sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

Script to change the database compatibility level

Leave a reply

Use below script to change the database compatibility level

ALTER DATABASE DatabaseName
SET SINGLE_USER
GO
EXEC sp_dbcmptlevel DatabaseName, 90;
GO
ALTER DATABASE DatabaseName
SET MULTI_USER
GO

SQL Server Version Compatibility Level
SQL Server 6.5 65
SQL Server 7.0 70
SQL Server 2000 80
SQL Server 2005 90
SQL Server 2008 100