Server Information Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Server Information Script

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-09-28 : 05:34:03

Hi All,

Firstly Thanks for Looking,

i am trying to build a DBA Database which will hold information of all our SQL servers accross our networks.

I have built the following tables to hold the relevant information; its a start and i need to expand on what data to hold......

------------------------------------------------------
-- Database information
------------------------------------------------------
USE [SQLServers]
GO
/****** Object: Table [dbo].[Databases] Script Date: 09/28/2009 10:24:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Databases](
[ServerName] [varchar](max) NULL,
[DatabaseName] [varchar](max) NULL,
[DateCreated] [datetime] NULL,
[CompatibilityLevel] [varchar](max) NULL,
[Size] [varchar](max) NULL,
[DatabaseOwner] [varchar](max) NULL,
[DatabaseState] [varchar](max) NULL,
[Instance] [varchar](max) NULL,
[MDFLocation] [varchar](max) NULL,
[LDFLocation] [varchar](max) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
------------------------------------------------------
-- Maintenance Plan information
------------------------------------------------------
USE [SQLServers]
GO
/****** Object: Table [dbo].[MaintenancePlan] Script Date: 09/28/2009 10:26:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MaintenancePlan](
[ServerName] [varchar](max) NULL,
[MaintenacePlanName] [varchar](max) NULL,
[Description] [varchar](max) NULL,
[PlanRotation] [varchar](max) NULL,
[BackupDirectory] [varchar](max) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
------------------------------------------------------
-- Server information
------------------------------------------------------
USE [SQLServers]
GO
/****** Object: Table [dbo].[Servers] Script Date: 09/28/2009 10:26:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Servers](
[Name] [nvarchar](50) NULL,
[IP Address] [nvarchar](50) NULL,
[Domain] [nvarchar](50) NULL,
[Version] [nvarchar](50) NULL,
[Build] [nvarchar](50) NULL,
[SP] [nvarchar](50) NULL,
[Edition] [nvarchar](50) NULL,
[Port] [nvarchar](50) NULL,
[OS] [nvarchar](50) NULL,
[Description] [nvarchar](50) NULL,
[CollationID] [nvarchar] (50) NULL,
[InstanceName] [nvarchar](50) NULL,
[IsClustered] [nvarchar] (50) NULL,
[IsFullTextInstalled] [nvarchar](50) NULL
) ON [PRIMARY]

i can populate Serverinformation with..............
SELECT
SERVERPROPERTY('CollationID') AS [Collation ID],
SERVERPROPERTY('ComparisonStyle') AS [Comparison Style],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [Computer Name Physical NetBIOS],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('EngineEdition') AS [Engine Edition],
SERVERPROPERTY('InstanceName') AS [Instance Name],
SERVERPROPERTY('IsClustered') AS [Is Clustered],
SERVERPROPERTY('IsFullTextInstalled') AS [Is FullText Installed],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [Is Integrated Security Only],
SERVERPROPERTY('LCID') AS [LCID],
SERVERPROPERTY('LicenseType') AS [License Type],
SERVERPROPERTY('MachineName') AS [Machine Name],
SERVERPROPERTY('NumLicenses') AS [Num Licenses],
SERVERPROPERTY('ProcessID') AS [Process ID],
SERVERPROPERTY('ProductVersion') AS [Product Version],
SERVERPROPERTY('ProductLevel') AS [Product Level],
SERVERPROPERTY('ResourceLastUpdateDateTime') AS [Resource Last Update DateTime],
SERVERPROPERTY('ResourceVersion') AS [Resource Version],
SERVERPROPERTY('ServerName') AS [Server Name],
SERVERPROPERTY('SqlCharSet') AS [Sql CharSet],
SERVERPROPERTY('SqlCharSetName') AS [Sql CharSet Name],
SERVERPROPERTY('SqlSortOrder') AS [Sql Sort Order],
SERVERPROPERTY('SqlSortOrderName') AS [Sql Sort Order Name];
GO

i need some help on the others.......

I want to build a script that will poulate all three tables at the sametime.

Cheers

Pete

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources