Programming - SQL - Microsoft SQL

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
 
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
select [name]
from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
 
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
 
--Open the cursor
OPEN tableCursor
 
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
 
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName
 
    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END
 
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
 
--Select all records so we can use the reults
SELECT *
FROM #TempTable
ORDER BY CAST(LEFT(ReservedSize,(LEN(ReservedSize) - 3)) AS INT)
 
--Final cleanup!
DROP TABLE #TempTable
 
GO

SELECT TBL.name AS ObjName 
      ,STAT.row_count AS StatRowCount 
      ,STAT.used_page_count * 8 AS UsedSizeKB 
      ,STAT.reserved_page_count * 8 AS RevervedSizeKB 
FROM tempdb.sys.partitions AS PART with(nolock)
     INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT with(nolock)
         ON PART.partition_id = STAT.partition_id 
            AND PART.partition_number = STAT.partition_number 
     INNER JOIN tempdb.sys.tables AS TBL with(nolock)
         ON STAT.object_id = TBL.object_id 
ORDER BY STAT.reserved_page_count desc,TBL.name;

Code:

SELECT 'DROP TABLE g2user.' + name FROM sys.tables

Partial output:

DROP TABLE g2user.g2_SequenceLock
DROP TABLE g2user.g2_SequenceId
DROP TABLE g2user.g2_ExternalIdMap
DROP TABLE g2user.g2_FailedLoginsMap
DROP TABLE g2user.g2_AccessMap

Code:

SELECT 'EXECUTE sp_rename ''g2user.' + name + ''', ''' + replace(name,'g2test','g2')+ '''' FROM sys.tables

Partial output:

EXECUTE sp_rename 'g2user.g2test_SessionMap', 'g2_SessionMap'
EXECUTE sp_rename 'g2user.g2test_TkOperatnMap', 'g2_TkOperatnMap'
EXECUTE sp_rename 'g2user.g2test_TkOperatnMimeTypeMap', 'g2_TkOperatnMimeTypeMap'
EXECUTE sp_rename 'g2user.g2test_TkOperatnParameterMap', 'g2_TkOperatnParameterMap'

SELECT 'DROP DATABASE [' + name + ']' FROM SYS.Databases

Partial output:

DROP DATABASE [master]
DROP DATABASE [tempdb]
DROP DATABASE [model]
DROP DATABASE [msdb]

For all databases except the system databases:

SELECT 'DROP DATABASE [' + name + ']' FROM SYS.Databases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

Bill Graziano's SQL Server Blog - Scripting out SQL Server Logins

Associate a newly created SQL login account with an existing user in a restored database:

ALTER USER loginnaam WITH LOGIN = loginnaam

Transaction log multiple GB in size, database a few hundred MB and no need to keep the transaction log?

Then try the following after making a backup of the database:

BACKUP LOG [databasename] WITH truncate_only
USE [databasename]
DBCC SHRINKFILE ('databasename_log', 0)

Where databasenaam_log must be replaced by the logical name of the log (examine the properties of the database in SQL Server Management Studio).

On SQL Server 2012 (and higher?) the truncate_only option has been removed.
To remove the transaction log in SQL Server 2012 the recovery model of the database must be switched to simple and then back to full. After this you should be able to shrink the transaction log file.

Use the following SQL statement:

--Set it to simple
USE master
ALTER DATABASE [databasename] SET RECOVERY SIMPLE
 
---Set it to full
USE master
ALTER DATABASE [databasename] SET RECOVERY FULL

Sources:
sql newsgroups - Live server running out of space - Can't reduce Transaction Log!
SQL Server Log File Maintenance
Microsoft TechNet - SQL Server 2012 - BACKUP (Transact-SQL)
Microsoft TechNet - SQL Server 2012 - View or Change the Recovery Model of a Database (SQL Server)

If your database mode is Simple and you have a log file of several GB with no current activity on the database server to justify such a large logfile, you may have an open transaction in the log file.

Using the following you can check the ID and start date of the oldest open transaction in the log:

USE [databasename]
DBCC OPENTRAN

If there are open transactions, the result should look something like this:

Transaction information for database 'DB'.

Oldest active transaction:
    SPID (server process ID): 67
    UID (user ID) : -1
    Name          : implicit_transaction
    LSN           : (26506:575:6)
    Start time    : Sep  7 2011  8:50:07:497AM
    SID           : 
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If there are no open transactions, the result should look like this:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you are sure that the open transaction is no longer necessary, you can end it with:

KILL spid

Where spid must be replaced by the server process ID from the result of DBCC OPENTRAN.
You might have to repeat this several times to kill all open transactions. Always make sure that the open transaction you want to kill isn't worth saving.
After this you can retry the log shrink.

See also:
MSDN Blogs > Blogs from Suhas > Shrinking the Transaction Log files in SQL Server

Place a database in single user mode:

ALTER DATABASE db-name SET SINGLE_USER

Place a database in multi-user mode:

ALTER DATABASE db-name SET MULTI_USER

Source:Set a single database into Single User Mode

Force a database repair allowing data loss:

DBCC CHECKDB ('dbnaam', repair_allow_data_loss)

The following creates a new column with data type char(1):

ALTER TABLE tabelnaam
ADD nieuwekolomnaam char(1) 

Source:TECH on the net - SQL: ALTER TABLE Statement and MSDN - Transact-SQL Reference (SQL Server 2000) ALTER TABLE

SELECT COUNT (*) FROM TableName

The following snippet inserts IP-adresses starting from 10.1.12.100-10.1.12.130 to 10.1.18.100-10.1.18.130 and two 0 values to a table. Modify numbers, run once as-is and then remove the “—” before the insert to actually apply the changes to the table.

DECLARE @i INT,@i2 INT,@String VARCHAR(30)
 
SELECT @i = 12,@i2 = 100,@String = '10.1.'
 
WHILE @i <= 18
BEGIN
 
	WHILE @i2 <= 130
	BEGIN
 
		---INSERT TableName
		SELECT @String + CAST(@i AS VARCHAR) + '.' + CAST(@i2 AS VARCHAR),0,0
 
		SELECT @i2 = @i2 + 1
 
	END
 
	SELECT
		@i = @i + 1,
		@i2 = 128
 
END

SELECT db_name(dbid) as DatabaseName,hostname,program_name,loginame,spid
---SELECT *
FROM sys.sysprocesses
WHERE dbid > 0
AND db_name(dbid) = 'databasenaam'

Or info from one of the following:

SELECT * FROM sys.dm_exec_connections 
SELECT * FROM sys.dm_exec_sessions

Show locks:

SELECT db_name(resource_database_id) AS DatabaseName,* FROM sys.dm_tran_locks

Sources:
SQL Server Curry - How to see active connections for each Database in SQL Server 2005
itechtalk - Monitoring Processes in T-SQL

SELECT name,recovery_model_desc
FROM master.sys.databases
--WHERE recovery_model_desc LIKE '%full%'
--WHERE recovery_model_desc LIKE '%simple%'

Get a list of job runs that ran longer than 59 minutes (run_duration > 5900):

SELECT *
FROM msdb.dbo.sysjobhistory
WHERE 
	step_id = 0
	AND run_duration > 5900

SELECT 'SELECT * FROM [' + name + '].sys.database_files'
FROM sys.databases;