To check Server level configure settings

SP_configure

--------- To check Advance Server level configuration settings

SP_configure 'show advanced options' ,1
reconfigure

To find all databases data files size and free space

  

IF OBJECT_ID('tempdb..#DBFiles') IS NOT NULL

    DROP TABLE #DBFiles

CREATE TABLE #DBFiles

(

[id] INT IDENTITY(1,1), 

[DBName] VARCHAR(200),

[Recovery] VARCHAR(200) NULL,

[File_Name] VARCHAR(200) NULL,

[Type_Desc] VARCHAR(20) NULL,

[physical_name] VARCHAR(MAX) NULL,

[FileSize_MB] DECIMAL(10,2) NULL,

[UsedSpace_MB] DECIMAL(10,2) NULL,

[FreeSpace_MB] DECIMAL(10,2) NULL,

[FreeSpace_%] DECIMAL(10,2) NULL,

[State_Desc] VARCHAR(20) NULL,

[AutoGrow] VARCHAR(200) NULL

)

INSERT INTO #DBFiles

SELECT 

db.[name] AS [DBName],

db.recovery_model_desc,

files.[name] AS [File_Name],

files.[type_desc],

files.physical_name,

CONVERT(DECIMAL(10,2), files.SIZE/128.0) AS [FILESIZE_MB],

NULL AS [UsedSpace_MB],

NULL AS [FreeSpace_MB],

NULL AS [FreeSpace_%],

files.state_desc,

CASE is_percent_growth 

WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'

        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -'

ELSE '' END 

        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'

ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB'

END 

        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting! - dbtales.com]'

ELSE ''

END AS [AutoGrow]

FROM sys.master_files AS files

INNER JOIN sys.databases AS db

ON files.database_id = db.database_id

WHERE db.[name] NOT IN ('model','tempdb','msdb','master');

EXEC sp_msforeachdb '

USE [?];

UPDATE #DBFiles

SET [USEDSPACE_MB] = CONVERT(DECIMAL(10,2), SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(dbf.[name], ''SpaceUsed'') AS INT)/128.0)),

[FREESPACE_MB] = CONVERT(DECIMAL(10,2), dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0),

[FREESPACE_%] = CONVERT(DECIMAL(10,2), ((dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0)/(dbf.SIZE/128.0))*100)

FROM #DBFiles AS temp

INNER JOIN sys.database_files AS dbf 

ON temp.[physical_name] = dbf.[physical_name] COLLATE DATABASE_DEFAULT '

SELECT * FROM #DBFiles ORDER BY DBName

To find all replication tables

-- create table with only the names of databases that are published

SELECT name as [DatabaseName]

INTO #tmpPubDatabases

FROM sys.databases

WHERE database_id > 4

AND is_published = 1; 

-- create table to hold the table info (name, schema,row count, space used)

CREATE TABLE #tmpTableSizes(

DBName VARCHAR(256),

SchemaName VARCHAR(256),

TableName VARCHAR(256),

RowCounts INT,

TotalSpaceMB DECIMAL(18,2)

);

DECLARE @command VARCHAR(MAX);

-- run this in all the databases that have publications 

SET @command = '

USE [?]

IF DB_NAME() IN (SELECT DatabaseName FROM #tmpPubDatabases)

BEGIN

INSERT #tmpTableSizes

SELECT 

db_Name(),

s.Name AS SchemaName,

t.NAME AS TableName,

p.rows AS RowCounts,

(SUM(a.total_pages) * 8)/1024.0 AS TotalSpaceMB

FROM 

sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

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

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE 

t.NAME NOT LIKE ''dt%'' 

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

--AND t.Name IN ()

GROUP BY 

t.Name, s.Name, p.Rows

ORDER BY 

t.Name

END';

-- run for all affected databases

EXEC sp_MSforeachdb @command

-- this will match the publications to the tables and give the you row count and sizes

-- run this in the distribution database

SELECT

     P.[publication]   AS [PublicationName]

    ,A.[publisher_db]  AS [DatabaseName]

    ,A.[article]       AS [ArticleName]

    ,A.[source_owner]  AS [Schema]

    ,A.[source_object] AS [Table]

,T.RowCounts

,T.TotalSpaceMB

FROM

    [distribution].[dbo].[MSarticles] AS A

    INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id])

LEFT JOIN #tmpTableSizes T ON A.[publisher_db] = T.DBName AND A.[source_owner] = T.SchemaName AND A.[source_object] = T.TableName

ORDER BY

    P.[publication], A.[article];

-- clean up

DROP TABLE #tmpTableSizes;

DROP TABLE #tmpPubDatabases;

How to change all DB's compatibility level at a time

USE [master]

GO

select 'ALTER DATABASE ['+name+'] SET COMPATIBILITY_LEVEL = 130' from sys.databases where database_id > 4

Change all Database owners at a time

USE [master]

GO

select 'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO sa' from sys.databases 

To find list of the job names and step associated with a particular table

Use MSDB

go

SELECT 

    [sJOB].[job_id] AS [JobID]

    , [sJOB].[name] AS [JobName]

    ,step.step_name

    ,step.command

FROM

    [msdb].[dbo].[sysjobs] AS [sJOB]

    LEFT JOIN [msdb].dbo.sysjobsteps step ON sJOB.job_id = step.job_id

WHERE step.command LIKE '%MYTABLENAME%'

ORDER BY [JobName]