SQL Server Uptime, SQL 2008 onwards

SELECT DATEDIFF(day,[sqlserver_start_time],GETDATE()) AS [Days SQL Server Has Been Up]

FROM sys.dm_os_sys_info;

Moving User Database Files

Use the following Script to check the location of your database files, and the logical name of the file:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Database Name Here');
GO

1.) Run the following Transact SQL to change location of your database files

ALTER DATABASE model MODIFY FILE ( NAME = logical name here , FILENAME = 'Type_New_Location_Here\physical database file name here.mdf' );

ALTER DATABASE model MODIFY FILE ( NAME = logical name here , FILENAME = 'Type_New_Location_Here\physical database file name here.ldf' );

2.) Take Database Offline

ALTER DATABASE [Database_Name] SET OFFLINE;

3.) Copy Physical Files to new location
4.) Set Database back online.

ALTER DATABASE [Database_Name] SET ONLINE;

Check new file location for Databases:


SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Database Name Here');
GO

5.) Once you are completely happy that the files are in the correct location, delete them from the old location.

Database File Size and Free Space

USE [Database Name];

SELECT [name] AS [File Name]

      ,[physical_name] AS [Physical Name]

      ,[size]/128 AS [Total Size in MB]

      ,[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]

FROM sys.database_files;

Row Count Per Table in a Database

Without querying the actual table:

SELECT SCHEMA_NAME(t.schema_id) SchemaName,

   t.[name] TableName,

   SUM(p.row_count) TotalRows

FROM sys.tables t

INNER JOIN sys.dm_db_partition_stats p

ON t.object_id = p.object_id

AND t.type_desc = 'USER_TABLE'

AND p.index_id IN (0,1)

-- WHERE t.[name] = 'Table_Name Required'

GROUP BY t.schema_id, t.[name]

ORDER BY TotalRows DESC

Very bad way of doing it!!!

USE [Database Name]

DECLARE @Table    VARCHAR(128)

DECLARE @Schema     VARCHAR(128)

DECLARE @SQL      VARCHAR(1000)

DECLARE @Count    Varchar(1000)

CREATE TABLE #tableList

            (

             [SchemaName]       VARCHAR(128)

            ,[TableName]      VARCHAR(128)

            ,[ID]             BIT

            );

          

CREATE TABLE #tableCount

            (

             [SchemaName]       VARCHAR(128)

            ,[TableName]      VARCHAR(128)

            ,[RowCount]         INT

            );

INSERT INTO #tableList

            SELECT SCHEMA_NAME(schema_id) AS SchemaName

                              ,name AS TableName

                              ,0

                  FROM sys.tables

            ORDER BY SCHEMA_NAME(schema_id),[name];

          

WHILE (SELECT COUNT(*) FROM #tableList WHERE [ID] = 0) > 0

            BEGIN

                        SELECT TOP 1 @Table = [TableName]

                                                      ,@Schema = [SchemaName]

                        FROM #tableList

                        WHERE ID = 0;

                      

      SET @Count = 'SELECT COUNT(*) FROM [' + @Schema + '].[' + @Table + ']';

                      

      SET @SQL = 'INSERT INTO #tableCount ([SchemaName], [TableName], [RowCount]) VALUES ('''+ @Schema + ''',''' + @Table + ''',(' + @Count + '))';

    

      EXEC (@SQL);

      IF @@ERROR<>0 PRINT @SQL;

    

      UPDATE #tableList

      SET [ID] = 1

      WHERE [TableName] = @Table

      AND [SchemaName] = @Schema;

    

            END

SELECT *

FROM #tableCount

ORDER BY [SchemaName],[TableName];

DROP TABLE #tableCount;

DROP TABLE #tableList;