Azure – MSSQL Server Information

Same as MSSQL Server Information but is specific to what will work in Azure

-- Basic Information
SELECT @@Servername AS [Server Name]
    --,@@ServiceName AS [Service Instance]
        ,Name AS [Database Name]
    ,create_date AS [Service Started]
    ,DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS [Days Running]
    ,DATEDIFF(s, create_date, GETDATE()) AS [Seconds Runnig]
    ,@@VERSION AS [Server Version]
FROM sys.databases
GO

-- All Databases
SELECT @@SERVERNAME AS SERVER
    ,NAME AS DBName
    ,recovery_model_Desc AS RecoveryModel
    ,Compatibility_level AS CompatiblityLevel
    ,create_date
    ,state_desc
FROM sys.databases
ORDER BY NAME;

-- Active User Connections by Database
SELECT  @@Servername AS Server ,
        DB_NAME(database_id) AS DatabaseName ,
        COUNT(database_id) AS Connections ,
        Login_name AS LoginName ,
        MIN(Login_Time) AS Login_Time ,
        MIN(COALESCE(last_request_end_time, last_request_start_time))
                                                         AS Last_Batch
FROM    sys.dm_exec_sessions
WHERE   database_id > 0
        AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )
GROUP BY database_id ,
         login_name
ORDER BY DatabaseName;

-- Database Location
SELECT @@Servername AS SERVER
    ,DB_NAME() AS DB_Name
    ,File_id
    ,Type_desc
    ,NAME
    ,LEFT(Physical_Name, 1) AS Drive
    ,Physical_Name
    ,RIGHT(physical_name, 3) AS Ext
    ,Size
    ,Growth
FROM sys.database_files
ORDER BY File_id;
GO

-- List Views
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DB_Name
    ,o.NAME AS 'ViewName'
    ,o.Type
    ,o.create_date
    ,sm.[DEFINITION] AS 'View script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'V' -- View
ORDER BY o.NAME;
GO

-- List Synonymns
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DBName
    ,s.NAME AS synonyms
    ,s.create_date
    ,s.base_object_name
FROM sys.synonyms s
ORDER BY s.NAME;
GO

-- List Stored Procedures
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DB_Name
    ,o.NAME AS 'ViewName'
    ,o.[type]
    ,o.Create_date
    ,sm.[definition] AS 'Stored Procedure script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id
WHERE o.[type] = 'P' -- Stored Procedures
    -- AND sm.[definition] LIKE '%insert%'
    -- AND sm.[definition] LIKE '%update%'
    -- AND sm.[definition] LIKE '%delete%'
    -- AND sm.[definition] LIKE '%tablename%'
ORDER BY o.NAME;
GO

-- List Functions
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DB_Name
    ,o.NAME AS 'FunctionName'
    ,o.[type]
    ,o.create_date
    ,sm.[DEFINITION] AS 'Function script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.[Type] = 'FN' -- Function
ORDER BY o.NAME;
GO

-- List Triggers
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DB_Name
    ,OBJECT_NAME(Parent_object_id) AS TableName
    ,o.NAME AS 'TriggerName'
    ,o.Type
    ,o.create_date
    ,sm.[DEFINITION] AS 'Trigger script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'TR' -- Triggers
ORDER BY o.NAME;
GO

-- Check Contraints
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DBName
    ,OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName
    ,OBJECT_NAME(parent_object_id) AS TableName
    ,parent_column_id AS Column_NBR
    ,NAME AS CheckConstraintName
    ,type
    ,type_desc
    ,create_date
    ,OBJECT_DEFINITION(object_id) AS CheckConstraintDefinition
FROM sys.Check_constraints
ORDER BY TableName
    ,SchemaName
    ,Column_NBR
GO

-- Table Definitions
SELECT @@Servername AS SERVER
    ,DB_NAME() AS DBName
    ,isc.Table_Name AS TableName
    ,isc.Table_Schema AS SchemaName
    ,Ordinal_Position AS Ord
    ,isc.Column_Name
    ,Data_Type
    ,Numeric_Precision AS Prec
    ,Numeric_Scale AS Scale
    ,Character_Maximum_Length AS [Len]
    ,-- -1 means MAX like Varchar(MAX)
    Is_Nullable
    ,Column_Default
    ,Table_Type
FROM INFORMATION_SCHEMA.COLUMNS isc
INNER JOIN information_schema.tables ist ON isc.table_name = ist.table_name
-- WHERE Table_Type = 'BASE TABLE' -- 'Base Table' or 'View'
ORDER BY DBName
    ,TableName
    ,SchemaName
    ,Ordinal_position;

-- Column Defaults
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DB_Name
    ,OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
    ,t.NAME AS TableName
    ,c.Column_ID AS Ord
    ,c.NAME AS Column_Name
    ,OBJECT_NAME(default_object_id) AS DefaultName
    ,OBJECT_DEFINITION(default_object_id) AS Defaults
FROM sys.Tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE default_object_id <> 0
ORDER BY TableName
    ,SchemaName
    ,c.Column_ID
GO

-- Computed Columns
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DBName
    ,OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
    ,t.NAME AS TableName
    ,c.Column_ID AS Ord
    ,c.NAME AS Computed_Column
FROM sys.Tables t
INNER JOIN sys.Columns c ON t.object_id = c.object_id
WHERE is_computed = 1
ORDER BY t.NAME
    ,SchemaName
    ,c.Column_ID
GO

-- Identity Columns
SELECT @@Servername AS ServerName
    ,DB_NAME() AS DBName
    ,OBJECT_SCHEMA_NAME(object_id) AS SchemaName
    ,OBJECT_NAME(object_id) AS TableName
    ,Column_id
    ,NAME AS IdentityColumn
    ,Seed_Value
    ,Last_Value
FROM sys.identity_columns
ORDER BY SchemaName
    ,TableName
    ,Column_id;
GO

-- Foreign Keys
SELECT f.NAME AS ForeignKey
    ,SCHEMA_NAME(f.SCHEMA_ID) AS SchemaName
    ,OBJECT_NAME(f.parent_object_id) AS TableName
    ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName
    ,SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName
    ,OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName
    ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ORDER BY TableName
    ,ReferenceTableName;
GO

-- Finding Foreign Keys missing Indexes
-- Foreign Keys missing indexes
-- Note this script only works for creating single column indexes.
-- Multiple FK columns are out of scope for this script.
SELECT DB_NAME() AS DBName
    ,rc.Constraint_Name AS FK_Constraint
    ,
    -- rc.Constraint_Catalog AS FK_Database,
    -- rc.Constraint_Schema AS FKSch,
    ccu.Table_Name AS FK_Table
    ,ccu.Column_Name AS FK_Column
    ,ccu2.Table_Name AS ParentTable
    ,ccu2.Column_Name AS ParentColumn
    ,I.NAME AS IndexName
    ,CASE
        WHEN I.NAME IS NULL
            THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes
                                    WHERE object_id = OBJECT_ID(N''' + RC.Constraint_Schema + '.' + ccu.Table_Name + ''') AND name = N''IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ''') ' + 'CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ' ON ' + rc.Constraint_Schema + '.' + ccu.Table_Name + '( ' + ccu.Column_Name + ' ASC ) WITH (PAD_INDEX = OFF,
                                   STATISTICS_NORECOMPUTE = OFF,
                                   SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF,
                                   DROP_EXISTING = OFF, ONLINE = ON);'
        ELSE ''
        END AS SQL
FROM information_schema.referential_constraints RC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
LEFT JOIN sys.columns c ON ccu.Column_Name = C.NAME
    AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID)
LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID
    AND c.column_id = ic.column_id
    AND index_column_id = 1
-- index found has the foreign key
--  as the first column
LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID
    AND ic.index_Id = i.index_Id
WHERE I.NAME IS NULL
ORDER BY FK_table
    ,ParentTable
    ,ParentColumn;
GO