Restarting server on remorte desktop

command prompt:
Shutdown /r /f /m bltrdevm58123 /t 0

Drop and Create Foreign keys

This query generates the drop and create statements for foreign keys in SQL Server.
DECLARE
@constraint_name sysname,
@parent_schema sysname,
@parent_name sysname,
@referenced_object_schema sysname,
@referenced_object_name sysname,
@column_name sysname,
@referenced_column_name sysname,
@is_not_for_replication bit,
@is_not_trusted bit,
@delete_referential_action tinyint,
@update_referential_action tinyint,
@AddLine nvarchar(max),
@DropLine nvarchar(max),
@fkline nvarchar(max),
@pkline nvarchar(max),
@object_id int,
@parent_object_id int

DECLARE
@AddScript TABLE (line nvarchar(max))
DECLARE
@DropScript TABLE (line nvarchar(max))

SET NOCOUNT ON

-- Create cursor for foreign keys system view
DECLARE cFKeys CURSOR READ_ONLY
FOR
SELECT object_id,
parent_object_id,
OBJECT_SCHEMA_NAME(parent_object_id),
object_name (parent_object_id),
[name],
is_not_trusted,
OBJECT_SCHEMA_NAME(referenced_object_id),
object_name(referenced_object_id),
delete_referential_action,
update_referential_action,
is_not_for_replication
FROM sys.foreign_keys

OPEN cFKeys

-- Collect basic data
FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- Start creating command string. One for add and one for drop constraint
SET @AddLine = N'ALTER TABLE ' + quotename(@parent_schema) + N'.' + quotename(@parent_name)
SET @DropLine = N'ALTER TABLE ' + quotename(@parent_schema) + N'.' + quotename(@parent_name)
-- Check if it is enabled or not
IF @is_not_trusted = 1
SET @AddLine = @AddLine + N' WITH NOCHECK'
ELSE
SET @AddLine = @AddLine + N' WITH CHECK'

SET @AddLine = @AddLine + N' ADD CONSTRAINT ' + quotename(@constraint_name) + N' FOREIGN KEY ('
SET @DropLine = @DropLine + N' DROP CONSTRAINT ' + quotename(@constraint_name)

-- Gather all columns for current key from foreign key columns system view
DECLARE cColumns CURSOR READ_ONLY
FOR
SELECT fc.name, pc.name
FROM sys.foreign_key_columns fk
inner join sys.columns fc on fk.parent_object_id = fc.object_id and fk.parent_column_id = fc.column_id
inner join sys.columns pc on fk.referenced_object_id = pc.object_id and fk.referenced_column_id = pc.column_id
WHERE parent_object_id = @parent_object_id and fk.constraint_object_id = @object_id

OPEN cColumns

SET @fkline = N''
SET @pkline = N''

FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- One line for column list and one for referenced columns
SET @fkline = @fkline + @column_name
SET @pkline = @pkline + @referenced_column_name
END
FETCH NEXT FROM cColumns INTO @column_name, @referenced_column_name
IF (@@fetch_status = 0)
BEGIN
SET @fkline = @fkline + ', '
SET @pkline = @pkline + ', '
END
END

CLOSE cColumns
DEALLOCATE cColumns
-- Add column list
SET @AddLine = @AddLine + @fkline + N')' + CHAR(13)
-- Add referenced table and column list
SET @AddLine = @AddLine + 'REFERENCES ' + quotename(@referenced_object_schema) + N'.' + quotename(@referenced_object_name)
SET @AddLine = @AddLine + N' (' + @pkline + N')'
-- Check the referential action that was declared for this key as well as replication option
SET @AddLine = @AddLine +
' ON DELETE' +
CASE @delete_referential_action
WHEN 1 THEN N' CASCADE'
WHEN 2 THEN N' SET NULL'
WHEN 3 THEN N' SET DEFAULT'
ELSE N' NO ACTION'
END +
' ON UPDATE' +
CASE @UPDATE_REFERENTIAL_ACTION
WHEN 1 THEN N' CASCADE'
WHEN 2 THEN N' SET NULL'
WHEN 3 THEN N' SET DEFAULT'
ELSE N' NO ACTION'
END +
CASE
WHEN @IS_NOT_FOR_REPLICATION = 1 THEN N' NOT FOR REPLICATION'
ELSE N''
END
-- Insert command into table for later use
INSERT INTO @DropScript SELECT @DropLine
INSERT INTO @AddScript SELECT @AddLine

FETCH NEXT FROM cFKeys INTO @object_id, @parent_object_id, @parent_schema, @parent_name, @constraint_name, @is_not_trusted, @referenced_object_schema, @referenced_object_name, @delete_referential_action, @update_referential_action, @is_not_for_replication
END
END

CLOSE cFKeys
DEALLOCATE cFKeys

SET NOCOUNT OFF

SELECT line FROM @DropScript
SELECT line FROM @AddScript

SQL Server query All HEAPS

This query pulls all the tables in SQL Server that are HEAPS. That is all tables without a cluster.

SELECT

SCHEMA_NAME(o.schema_id) AS [Schema]

,object_name(i.object_id ) AS [Tables]

,p.rows AS ROWS

,user_seeks

,user_scans

,user_lookups

,user_updates

,last_user_seek

,last_user_scan

,last_user_lookup

, i.type_desc

FROM sys.indexes i

INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id

WHERE i.type_desc = 'HEAP'

ORDER BY user_updates Desc

Tables without Primary keys

SELECT SCHEMA_NAME(o.schema_id) AS [Schema]

,object_name(i.object_id ) AS [Tables]
,p.rows AS ROWS
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
, i.type_desc
FROM sys.indexes i

INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.type_desc = 'HEAP'
ORDER BY user_updates Desc

How to Attach (or Reattach) a Database Missing the LDF

SQL Server 2005: How to Attach (or Reattach) a Database Missing the LDF
Home -> Database
3902 views

From the computer of: shamanstears (592 recipes)
Created: Nov 20, 2006 Updated: Nov 28, 2006

If you need to attach a database that has no log file (.LDF), or have deleted the log file because it was too big and need to reattach the database, SQL Server 2005 does allow you to do this. You can attach the database by following this Tech-Recipe:

1. Open the SQL Server Management Studio and connect to the desired server.

2. Right-click on Databases and select Attach.

3. Click the Add button and select the database file to be attached.

4. Under database details, select the .LDF file and click the Remove button.

5. Click OK.

The database will be attached with a new logfile.

SQL Server Version and SP Serveice Pack Level

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Standard Production DBA Tasks: DBCC CHECKDB

Standard Production DBA Tasks:

DBCC CHECKDB (Transact-SQL)
Updated: 1 February 2007
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
Validates the Service Broker data in the database.
This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB. For more detailed information about the checks that these commands perform, see the descriptions of these commands.

New SQL Server 2005 Features: DDL Trigger

New SQL Server 2005 Features: DDL Trigger

SS05 has a nice feature that allows you to control who is dinking with your tables and what they can do.

“A DDL Trigger can fire after execution of any Transact-SQL event that belongs to a predefined grouping of similar events. For example, if you want a DDL trigger to fire after any CREATE TABLE, ALTER TABLE, or DROP TABLE statement is run, you can specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER statement. After CREATE TRIGGER is run, the events that are covered by an event group are added to the sys.trigger_events catalog view.” http://msdn2.microsoft.com/en-us/library/ms186406.aspx

New SQL 2005 Features - On-line Restore.

Favorite SQL 2005 Features On-line Restore.
On-line Restore. This works well with restoring one filegroup while not bringing down the whole system

Ben Link Recruiter

Ben Link
Recruiter
Insight Global, Inc.
1420 Spring Hill Road
Suite 130
McLean, VA 22102
703.442.7717 phone
703.442.7716 fax
www.insightglobal.net"