Cacher is the code snippet organizer for pro developers

/** 
* Get all database connections
* http://stackoverflow.com/questions/4654291/how-to-get-detailed-list-of-connections-to-database-in-sql-server-2005
*/
DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX),
    SPID_1 INT,
    REQUESTID INT
)
INSERT INTO @AllConnections EXEC sp_who2
SELECT * FROM @AllConnections WHERE DBName = 'YourDatabaseName'

/**
* Set database offline
*/
USE master
GO
ALTER DATABASE [YourDatabaseName]
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

/**
* Count all tables data
*/
CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
SELECT SUM(row_count) AS total_row_count FROM #counts
DROP TABLE #counts

/**
* Search all columns names
*/
SELECT
  s.name as ColumnName
    ,sh.name+'.'+o.name AS ObjectName
    ,o.type_desc AS ObjectType
    ,CASE
         WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
         WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
        WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
         ELSE t.name
     END AS DataType

    ,CASE
         WHEN s.is_nullable=1 THEN 'NULL'
        ELSE 'NOT NULL'
    END AS Nullable
    ,CASE
         WHEN ic.column_id IS NULL THEN ''
         ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
     END
    +CASE
         WHEN sc.column_id IS NULL THEN ''
         ELSE ' computed('+ISNULL(sc.definition,'')+')'
     END
    +CASE
         WHEN cc.object_id IS NULL THEN ''
         ELSE ' check('+ISNULL(cc.definition,'')+')'
     END
        AS MiscInfo
FROM sys.columns                           s
    INNER JOIN sys.types                   t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
    INNER JOIN sys.objects                 o ON s.object_id=o.object_id
    INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
    LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
    LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
    LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
ORDER BY sh.name+'.'+o.name,s.column_id