6.Run SQL Query with Multi Database Query with Registered Servers

Problem

A user supports an application that has three databases for three different environments. These databases are located on two different SQL Servers and have different names. The user needs to validate application users on each of these databases. How can he/she run a single query in SQL Server Management Studio (SSMS) for all three databases?

Solution

In this tip we will show you how to run a query against multiple databases that are spread among different SQL Server instances. We will use the Registered Servers feature in SSMS. The same result could be achieved using a multiple servers query with Central Management Server (refer to this tip to find out how to use Central Management Server).

Pre-requisite

We will demonstrate the solution for this tip using two SQL Server instances and three databases:

• Production Environment:
  • SQL Server name - SQLDEMO1
  • Database Name - DemoApp_ProdDB
• Test Environment:
  • SQL Server name - SQLDEMO2
  • Database Name - DemoApp_TestDB
• QA Environment:
  • SQL Server name - SQLDEMO2
  • Database Name - DemoApp_QADB

Connect to each database and run this script to create a demo table:

CREATE TABLE dbo.ApplicationUser (Username VARCHAR(50), isActive TINYINT)
GO
INSERT INTO dbo.ApplicationUser (Username , isActive) VALUES ('John Smith', 1)
INSERT INTO dbo.ApplicationUser (Username , isActive) VALUES ('Peter Pan', 1)
INSERT INTO dbo.ApplicationUser (Username , isActive) VALUES ('Jack White', 0)
GO

On DemoApp_ProdDB database run this script to make some of the data a little different:

UPDATE [DemoApp_ProdDB].[dbo].[ApplicationUser]
   SET [isActive] = 0
 WHERE [Username] = 'John Smith'
GO

Register Servers

We will create the new Server Group "Demo App1" and register our two test SQL Servers under this group (refer to thistip to find out more about how to register multiple servers in SSMS).
Register the servers under the "Demo App1" Group as follows (note that we have the same "Server name" twice, but a different "Registered server name" each time, also we have different Default database for each connection):

Server Registration for the Production Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO1" as Server Name and "ProductionDB" as Registered server name: 

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database":

Click "Yes" to continue, select DemoApp_ProdDB database:

Click "Save".

Server Registration for the Test Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO2" as Server name and "TestDB" as Registered server name: 

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database", select DemoApp_TestDB database: 

Click "Save".

Server Registration for the QA Environment

Right click the "Demo App1" Server Group and click the "New Server Registration". Enter "SQLDEMO2" as Server name and "QADB" as Registered server name:

Click the "Connection Properties" tab, then "<Browse server...>" next to "Connect to database", select DemoApp_TestDB database:

Click "Save".

Run Multi-database Query

Under Registered Servers right click on the "Demo App1" SQL Server Group that we have created and click "New Query":

Run the following query:

SELECT Username, isActive, @@SERVERNAME as "Real Server Name", DB_NAME() as "Database Name"
FROM dbo.ApplicationUser 
WHERE Username = 'John Smith'

Review the results:

Note, that the "Server Name" column contains the name that we entered for the Registered Server:

Note also that we have connected to the different databases and that two of these databases are on the same SQL Server Instance (SQLDEMO2).

SQL Server DEMO Scripts

USE [master];
GO
--Create new database
CREATE DATABASE [NewDatabase]
-- Database configuration 

ALTER DATABASE [NewDatabase] SET COMPATIBILITY_LEVEL = 110;
GO
ALTER DATABASE [NewDatabase] SET AUTO_CLOSE OFF; 
GO
ALTER DATABASE [NewDatabase] SET AUTO_SHRINK OFF; 
GO
ALTER DATABASE [NewDatabase] SET AUTO_CREATE_STATISTICS ON; 
GO
ALTER DATABASE [NewDatabase] SET AUTO_UPDATE_STATISTICS ON; 
GO
ALTER DATABASE [NewDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC ON; 
GO
ALTER DATABASE [NewDatabase] SET QUOTED_IDENTIFIER OFF; 
GO

-- Make sure page verify is set to CHECKSUM
ALTER DATABASE [NewDatabase] SET PAGE_VERIFY CHECKSUM;  
GO
---RTO( recovery time objectives)  in database level in sec over written is instance level 
ALTER DATABASE [NewDatabase] SET TARGET_RECOVERY_TIME = 30 SECONDS; 
GO
--- Change state mode 
ALTER DATABASE [NewDatabase]SET OFFLINE
ALTER DATABASE [NewDatabase]SET ONLINE
SELECT  [name] ,[state] ,[state_desc]FROM sys.[databases]
-- change  read  write mode 
ALTER DATABASE [NewDatabase] SET  READ_ONLY; 
GO
ALTER DATABASE [NewDatabase] SET  READ_WRITE; 
GO
-- Make sure you consider what recovery model you will be using
ALTER DATABASE [NewDatabase] SET  SINGLE_USER
GO
ALTER DATABASE [NewDatabase] SET  RESTRICTED_USER
GO
ALTER DATABASE [NewDatabase] SET  MULTI_USER; 
GO
---SET COLLATE
--FIRST FIND WICH COLLATION 
SELECT  [name] ,[database_id] ,[collation_name]FROM sys.[databases];
GO
---SET TO NEW COLLATION
ALTER DATABASE [NewDatabase]COLLATE French_CS_AS;
GO
--SET TO DEFULT COLLATION
ALTER DATABASE [NewDatabase]COLLATE SQL_Latin1_General_CP1_CI_AS;

-- Cleanup
USE [master];
GO

DROP DATABASE [NewDatabase];
GO



---------------------


CREATE DATABASE
-------------------------------


--Lab Example # 1
USE [master];

-- Minimum file requirements = 1 primary file and
-- 1 transaction log file
CREATE DATABASE [DNTDemo] 
ON PRIMARY
  -- First file in primary filegroup is the primary file
( NAME = N'DNTDemo',-- logical_file_name   
 FILENAME = N'C:\Project\DNTDemo.mdf' ,  
  -- 'os_file_name' 
 SIZE = 1024MB ,
  -- size [MB ] 
 MAXSIZE = UNLIMITED,
  -- max_size [ MB| UNLIMITED 
 FILEGROWTH = 1024MB )
  -- growth_increment [ KB | MB | GB | TB | % ] 
LOG ON -- specifies log file details
( NAME = N'DNTDemo_log',
  -- logical_file_name
 FILENAME = N'C:\Project\DNTDemo_log.ldf' , 
  -- 'os_file_name' 
 SIZE = 1024MB ,
  -- size [  | MB | GB | TB ] ]
 MAXSIZE = 2048GB ,
  -- max_size [  | MB | GB | TB ] | UNLIMITED 
 FILEGROWTH = 1024MB);
 -- Avoid growth by percentage!

EXEC sp_helpdb 'DNTDemo';

-- Cleanup
USE [master];
GO

DROP DATABASE [DNTDemo];
GO

--Lab Example # 2

USE [master];

-- Multiple data files
-- A maximum of 32,767 files and 32,767 filegroups-- (not a goal, )
CREATE DATABASE [DNTDemo] 
ON PRIMARY 
( NAME = N'DNTDemo', 
 FILENAME = N'C:\Project\DNTDemo.mdf' , 
 SIZE = 1024MB , 
 FILEGROWTH = 1024MB ), 
( NAME = N'DNTDemo_2', 
 FILENAME = N'C:\Project\DNTDemo_2.ndf' , 
 SIZE = 1024MB , 
 FILEGROWTH = 1024MB ) 
LOG ON 
( NAME = N'DNTDemo_log', 
 FILENAME = N'C:\Project\DNTDemo_log.ldf' , 
 SIZE = 1024MB , 
 FILEGROWTH = 1024MB);
GO

--  multiple transaction log files
---Go online and serarch how to write this 

EXEC sp_helpdb 'DNTDemo';

-- Cleanup
USE [master];
GO

DROP DATABASE [DNTDemo];
GO

Create table bigtable
(id int identity not null,
Name char(8000))

select * from bigtable 
declare @count int=0
while @count <1000
begin insert bigtable values('a')
set @count+=1
end


select * from bigtable 



-- Individual File Sizes and space available for current database  
SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);


-- Look at some relevant database properties for this database
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model], db.state_desc, 
db.log_reuse_wait_desc AS [Log Reuse Wait Description], 
CONVERT(DECIMAL(18,2), ls.cntr_value/1024.0) AS [Log Size (MB)], CONVERT(DECIMAL(18,2), lu.cntr_value/1024.0) AS [Log Used (MB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %], 
db.[compatibility_level] AS [DB Compatibility Level], 
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced, 
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on,  db.is_cdc_enabled
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 
AND db.[name] = N'NewDatabase'
OPTION (RECOMPILE);


-----------




USE [master];
GO

CREATE DATABASE [dnt_demo] 
ON PRIMARY 
( NAME = N'dnt_demo', 
 FILENAME = N'C:\pROJECT\MDF\dnt_demo.mdf' , 
 SIZE = 4096MB , 
 FILEGROWTH = 1024MB ), 
( NAME = N'dnt_demo_2', 
 FILENAME = N'C:\pROJECT\MDF\dnt_demo_2.ndf' , 
 SIZE = 4096MB , 
 FILEGROWTH = 1024MB ) 
LOG ON 
( NAME = N'dnt_demo_log', 
 FILENAME = N'C:\pROJECT\LDF\dnt_demo_log.ldf' , 
 SIZE = 1024MB , 
 FILEGROWTH = 1024MB);
GO

-- Add another file
ALTER DATABASE [dnt_demo] 
ADD FILE 
( NAME = N'dnt_demo_3', 
  FILENAME = N'C:\pROJECT\MDF\dnt_demo_3.ndf' , 
  SIZE = 4096MB , 
  FILEGROWTH = 1024MB ) 
TO FILEGROUP [PRIMARY];
GO

-- Removing a file
ALTER DATABASE [dnt_demo]  
REMOVE FILE [dnt_demo_3];
GO

-- What if we have data in this file?
ALTER DATABASE [dnt_demo] 
ADD FILE 
( NAME = N'dnt_demo_3', 
  FILENAME = N'C:\pROJECT\MDF\dnt_demo_3.ndf' , 
  SIZE = 4096MB , 
  FILEGROWTH = 1024MB ) 
TO FILEGROUP [PRIMARY];
GO

USE [dnt_demo];
GO

CREATE TABLE dbo.[UseSpace]
([col01] INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1,1),
 [col02] NVARCHAR (4000) NOT NULL);
GO

SET NOCOUNT ON;
GO

INSERT dbo.[UseSpace]
([col02])
VALUES (REPLICATE (N'z', 4000));
GO 1000

-- Does this work?
USE [master];

ALTER DATABASE [dnt_demo]  
REMOVE FILE [dnt_demo_3];
GO

-- Empty file
USE [dnt_demo];

-- Migrates all data to other files in the same filegroup
DBCC SHRINKFILE ('dnt_demo_3', EMPTYFILE);

-- Does this work?
USE [master];
GO

ALTER DATABASE [dnt_demo]  
REMOVE FILE [dnt_demo_3];
GO

-- Cleanup
USE [master];
GO

DROP DATABASE [dnt_demo];
GO

How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password

1.Open command prompt with Administrator

2.Run the following command prompt to stop SQL service:

       "net stop mssqlserver"

3.Now go to the directory where SQL server is installed.

   Run cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn

4.Now run the following command to start sql server in single user mode.

  sqlservr -m"SQLCMD"

  Note:- for named instance need to suppy the instancename after -s switch example: id instancename is SQL2008R2 then need to run sqlservr -m"SQLCMD" -sSQL2008R2

5.Now open another command prompt window with as Administrator and write command follwoing command

  SQLCMD

6.Run following two commands view sourceprint?

  CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master];

  EXEC sys.sp_addsrvrolemember @loginame = N'testAdmin', @rolename = N'sysadmin';

  GO

  Press Enter.

7.Go back to first command window and press Ctrl+C to stop the SQL server and then type 'Y' for confirmation. This will stop the sql server.

8.Start SQL server again at services.msc

9.startup parameters need to specified this time.Now using SSMS, try to connect with

  "testAdmin" as user and "test@1234" as password.

10.Create your own logins

11.Drop testAdmin as it is not required any more.

Note: If you do not have SQL authentication enabled then you can try adding your windows user and replace setp-6 with below queries. Here <<DOMAIN\USERNAME>> is placeholder for your user name

view sourceprint?1.create login [<<DOMAIN\USERNAME>>] from windows; 

2.EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin'; 

3.GO;

Get a list of all the DBCC commands

DBCC HELP()
Shows the syntax of an individual co
DBCC activecursors [(spid)]
DBCC addextendedproc (function_name, dll_name)
DBCC addinstance (objectname, instancename)
DBCC adduserobject (name)
DBCC auditevent (eventclass, eventsubclass, success, loginname
, rolename, dbusername, loginid)
DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])
DBCC balancefactor (variance_percent)
DBCC bufcount [(number_of_buffers)]
DBCC buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ]
[, dirty | io | kept | rlock | ioerr | hashed ]]])
DBCC bytes ( startaddress, length )
DBCC cachestats
DBCC callfulltext
DBCC checkalloc [('database_name'[, NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]
DBCC checkcatalog [('database_name')] [WITH NO_INFOMSGS]
DBCC checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )]
[WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]
DBCC checkdb [('database_name'[, NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[, ALL_ERRORMSGS]
[, PHYSICAL_ONLY][, ESTIMATEONLY][,DBCC TABLOCK]
DBCC checkdbts (dbid, newTimestamp)]
DBCC checkfilegroup [( [ {'filegroup_name' | filegroup_id} ]
[, NOINDEX] )] [WITH NO_INFOMSGS
[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC checkident ('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )
DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ])
DBCC checktable ('table_name'[, {NOINDEX | index_id | REPAIR}])
[WITH NO_INFOMSGS[, ALL_ERRORMSGS]
[, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC cleantable ('database_name'|database_id, 'table_name'|table_id,[batch_size])
DBCC cacheprofile [( {actionid} [, bucketid])
DBCC clearspacecaches ('database_name'|database_id,
'table_name'|table_id, 'index_name'|index_id)
DBCC collectstats (on | off)
DBCC concurrencyviolation (reset | display | startlog | stoplog)
DBCC config
DBCC cursorstats ([spid [,'clear']])
DBCC dbinfo [('dbname')]
DBCC dbrecover (dbname [, IgnoreErrors])
DBCC dbreindex ('table_name' [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]
DBCC dbreindexall (db_name/db_id, type_bitmap)
DBCC dbrepair ('dbname', DROPDB [, NOINIT])
DBCC dbtable [({'dbname' | dbid})]
DBCC debugbreak
DBCC deleteinstance (objectname, instancename)
DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])]
DBCC detachdb [( 'dbname' )]
DBCC dropcleanbuffers
DBCC dropextendedproc (function_name)
DBCC dropuserobject ('object_name')
DBCC dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number}
| {'CLEAR', exception_number})
DBCC errorlog
DBCC extentinfo [({'database_name'| dbid | 0}
[,{'table_name' | table_id} [, {'index_name' | index_id | -1}]])]
DBCC fileheader [( {'dbname' | dbid} [, fileid])
DBCC fixallocation [({'ADD' | 'REMOVE'},
{'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'}
, filenum, pagenum [, objectid, indid])
DBCC flush ('data' | 'log', dbid)
DBCC flushprocindb (database)
DBCC free dll_name (FREE)
DBCC freeproccache
dbcc freeze_io (db)
dbcc getvalue (name)
dbcc icecapquery ('dbname', stored_proc_name
[, #_times_to_icecap (-1 infinite, 0 turns off)])
Use 'dbcc icecapquery (printlist)' to see list of SP's to profile.
Use 'dbcc icecapquery (icecapall)' to profile all SP's.
dbcc incrementinstance (objectname, countername, instancename, value)
dbcc ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } )
DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid |indname})
DBCC inputbuffer (spid)
DBCC invalidate_textptr (textptr)
DBCC invalidate_textptr_objid (objid)
DBCC iotrace ( { 'dbname' | dbid | 0 | -1 }
, { fileid | 0 }, bufsize, [ { numIOs | -1 }
[, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )
DBCC latch ( address [, 'owners'] [, 'stackdumps'])
DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}] |
[{'STALLREPORTTHESHOLD', stallthreshold}])
DBCC lockobjectschema ('object_name')
DBCC log ([dbid[,{0|1|2|3|4}[,['lsn','[0x]x:y:z']|['numrecs',num]|['xdesid','x:y']
|['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs',
{'lop'|op}...]|['output',x,['filename','x']]...]]])
DBCC loginfo [({'database_name' | dbid})]
DBCC matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'})
DBCC memobjlist [(memory object)]
DBCC memorymap
DBCC memorystatus
DBCC memospy
DBCC memusage ([IDS | NAMES], [Number of rows to output])
DBCC monitorevents ('sink' [, 'filter-expression'])
DBCC newalloc - please use checkalloc instead
DBCC no_textptr (table_id , max_inline)
DBCC opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]
DBCC outputbuffer (spid)
DBCC page ( {'dbname' | dbid}, filenum, pagenum
[, printopt={0|1|2|3} ][, cache={0|1} ])
DBCC perflog
DBCC perfmon
DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}
, targetfile, targetpg, order={1|0})
DBCC pintable (database_id, table_id)
DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid}
[, nbufs[, printopt = { 0 | 1 } ]]] )]
DBCC proccache
DBCC prtipage (dbid, objid, indexid [, [{{level, 0}
| {filenum, pagenum}}] [,printopt]])
DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]
DBCC readpage ({ dbid, 'dbname' }, fileid, pageid
, formatstr [, printopt = { 0 | 1} ])
DBCC rebuild_log (dbname [, filename])
DBCC renamecolumn (object_name, old_name, new_name)
DBCC resource
DBCC row_lock (dbid, tableid, set) - Not Needed
DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)
DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)
DBCC setcpuweight (weight)
DBCC setinstance (objectname, countername, instancename, value)
DBCC setioweight (weight)
DBCC show_statistics ('table_name', 'target_name')
DBCC showcontig (table_id | table_name [, index_id | index_name]
[WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])
DBCC showdbaffinity
DBCC showfilestats [(file_num)]
DBCC showoffrules
DBCC showonrules
DBCC showtableaffinity (table)
DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid[,option]}})
DBCC showweights
DBCC shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage
[, {NOTRUNCATE | TRUNCATEONLY}]])
DBCC shrinkfile ({fileid | 'filename'}, [compress_size
[, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])
DBCC sqlmgrstats
DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]}
| {THREADS} | {LOGSPACE})
DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, 'THREADID'}] )]
DBCC tab ( dbid, objid )
DBCC tape_control {'query' | 'release'}[,('\\.\tape')]

DBCC tec [( uid[, spid[, ecid]] )]

DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )]

DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST])

DBCC thaw_io (db)

DBCC traceoff [( tracenum [, tracenum ... ] )]

DBCC traceon [( tracenum [, tracenum ... ] )]

DBCC tracestatus (trace# [, ...trace#])

DBCC unpintable (dbid, table_id)

DBCC updateusage ({'database_name'| 0} [, 'table_name' [, index_id]])
[WITH [NO_INFOMSGS] [,] COUNT_ROWS]

DBCC upgradedb (db) DBCC usagegovernor (command, value)

DBCC useplan [(number_of_plan)]

DBCC useroptions DBCC wakeup (spid)

DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)
DBCC tec [( uid[, spid[, ecid]] )]
DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )]
DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST])
DBCC thaw_io (db)
DBCC traceoff [( tracenum [, tracenum ... ] )]
DBCC traceon [( tracenum [, tracenum ... ] )]
DBCC tracestatus (trace# [, ...trace#])
DBCC unpintable (dbid, table_id)
DBCC updateusage ({'database_name'| 0} [, 'table_name' [, index_id]])
[WITH [NO_INFOMSGS] [,] COUNT_ROWS]
DBCC upgradedb (db) DBCC usagegovernor (command, value)
DBCC useplan [(number_of_plan)]
DBCC useroptions DBCC wakeup (spid)
DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)

Shows the list of DBCC commands. then
If you run DBCC HELP on all the commands you end up with this list: