Topcoder Challenge | Topcoder Community

Challenge Overview

Challenge Summary

Welcome to “Compilation of SQL Server Properties Challenge”. Our client would like to compile all possible SQL Server properties of all of their product installations in one place for further analysis and possible standardization of some of the configurations.

This challenge is to develop a T-SQL script that can be run on servers hosting databases of our client’s product. When run, the script should compile all available properties of the server, database & session. Output of script is a compilation of all properties of of ther server and database on which it is run, produced in a very easy to consume format. For complete definition of format of the output from this script, “Guidelines for producing Output Script”.

Our client seeks community members’ expertise and creative thinking in meeting their needs.

Full Description & Project Guide

Some facts about client’s product installation

Product installations are spread over 100s of servers ranging from SQL Server 2005 to 2014 versions
An SQL Server instance may have multiple product databases
We need to gather property values from the database on which the script is run. If the database is not a product database, an error should be raised (for how to identify if a db is product database see Table Definition Note 1)

Project Requirements & Restrictions

Client will handle the process of actual running of the script on as many databases as needed. In other words, scope of the script is current database on which it is run.
Output of the script should list all properties described in Table 1 and their configured values (see guidelines for writing “Submission Script” and “Output Script”)
If you can think of other config settings that may be of use, you are welcome to extend SystemSettings table and include such config settings in the Submission Script. Please clearly mark any such addition using comments.

Final Submission Guidelines

Guidelines for writing Submission Script

The script you will be submitting for the challenge will be referred to as “Submission Script”
As a result of running Submission Script, an INSERT statement should be generated, targeting the table definition given below; where columns are from the target table below and values are property values read from server, db & session properties (The INSERT statement script generated by Submission Script will be referred to as “Output Script”; see Guidelines for producing Output Script for more details)
Submission Script will be delivered in a single SQL source file named “gather_sql_environment.sql”. The file shall contain only the script block; the dbo.SystemSettings table will be created by separate means.
Submission Script file format should be an 8-bit ASCII that uses a CarriageReturn/LineFeed pair as a line terminator.
Running Submission Script should not make permanent changes to the schema or data (except manipulating export_bcp table; see Table Definition export_bcp for details). Usage of temporary tables (table variables preferred) and temporary stored procs is acceptable.
Terminate all statements with a semi-colon.
Comment code blocks liberally.
Use code indentation for multi-line statements. Use spaces to indent, not tabs. Tab stops are every 2 characters.
Target a max of 150 characters per line for code and comments. Beyond column 135 you should strongly consider wrapping the line.
Capitalize all keywords.
Use meaningful variable names. The first 3 characters are lowercase and indicate the datatype of the variable. The rest of the characters are CamelCase without underscores. Examples: @chvSomeVar, @intSomeNumber

Data Type

Prefix

Example

Binary

bny

@bnyControlNum

Bit

bit

@bitErr

Char

chr

@chrParmCode

Datetime

dtm

@dtmNow

Decimal

dec

@decHours2

Float

flt

@fltMinutes

Int

int

@intEmployeeId

Money

mny

@mnyAmount

Numeric

num

@numBatchEarnings014

Nvarchar

ncv

@ncvSqlErrMessage

Real

rel

@relHourlyAmount

Smalldatetime

dts

@dtsStartDate

Smallint

ins

@insLength

Smallmoney

mns

@mnsDollars

Table

tbl

@tblOutputCache

Tinyint

iny

@inyBCPOutputFlag

Uniqueidentifier

uig

@uigHandle

Varbinary

bnv

@bnvSafeNum

Varchar

chv

@chvRedirSymbol

Commas follow column names, etc. Commas should not start a code line.
Set-based logic is preferred over loops for processing. Do not use cursors.
Code labels and GOTO statements should not be used.
Always use COLLATE DATABASE_DEFAULT for string fields in any declared table variables or temp tables. NULL or NOT NULL should always be specified.
TRY/CATCH blocks should be used for error handling. Precede every “BEGIN TRY” statement with a “SET NOCOUNT ON;” statement. This works around a compiler bug on certain versions of SQL 2012.

Guidelines for producing Output Script

The INSERT statement script generated by Submission Script will be referred to as “Output Script”
Output Script should be a syntactically and semantically valid INSERT statement targeting CompanySqlEnvironment.dbo.SystemSettings table. See Table Definition SystemSettings for details.
Generation of Output Script should use “xp_cmdshell bcp queryout” command
Table dbo.export_bcp should be used as source for “bcp queryout”. See Table Definition export_bcp for details.
export_bcp.session_guid is a newid() where ‘-‘s are removed
Notice that export_bcp.data is limited to 8000 characters whereas xmls generated for columns dbo.System_Settings.TE_ColumnAnsiPadding, dbo.System_Settings.TE_ColumnCollations, dbo.System_Settings.TE_Sproc_Settings dbo.System_Settings.TE_TablesWithDroppedColumns, and dbo.System_Settings.TE_ProductHistory are well over this limit. Values for these columns should be broken down without loss of information

Allowed Technology

T-SQL compatible with SQL Server 2005 (compatibility level 90)

Table Definition SystemSettings

dbo.SystemSettings.sql defines the table that output script should target with an INSERT statement. A separate SQL file will be included containing this code for your convenience.

Table Definition Note 1

The following query demonstrates an acceptable method of determining whether a SQL database is a Product database:

-- If any of the 9 tables in the query below don't exist in the database, it's not a Product database

WITH

CountOfProductTables

(TableCount)

(

SELECT COUNT(1) AS TableCount

FROM sys.tables

WHERE name IN (

'audit_log', 'company', 'employee',

'navigation_tree', 'product_history',

'ref_parameter_type', 'security_user',

'session', 'template_master'

)

SELECT

CASE TableCount WHEN 9 THEN 1 ELSE 0 END AS Is_Product_DB

FROM CountOfProductTables;

Table Definition Note 2

This should be built from the name column in Product’s company table. If the database is not a Product DB, NULL should be returned. Note that company_id = 0 should be ignored and not returned here. The XML should be formatted with a root called “companies” and 1-n elements called “company” containing the names. An example is shown below:

</companies>

For the purpose of this query, assume company table having columns company_id and name

Table Definition Note 3

The following query will return the version of a Product database. If the database is not Product, this query will fail.

SELECT parameter_string as ProductReportedVersion

FROM dbo.ref_parameter_type

WHERE parameter_type = 'RV';

For the purpose of this query, assume ref_parameter_type table having columns parameter_string and parameter_type.

Table Definition Note 4

This will be an XML-formatted list of the ansi padding settings stored with database string columns. It will be generated using logic similar to the query shown below.

SELECT

O.name AS TableName ,

C.name AS ColumnName ,

T.name AS DataType ,

C.is_ansi_padded AS IsAnsiPadded

FROM sys.columns AS C

JOIN sys.objects AS O ON C.object_id = O.object_id

JOIN sys.types AS T ON C.system_type_id = T.system_type_id

AND T.is_user_defined = 0

WHERE O.type = 'U'

AND C.collation_name IS NOT NULL

ORDER BY O.name, C.column_id

FOR XML PATH ('Column'), ROOT ('Columns');

An example of the format of the XML is below. There will be 1-n Column elements, one for each column returned by the query above:

<TableName>abra_hours_worked</TableName>

<DataType>varchar</DataType>

</Column>

<TableName>abra_hours_worked</TableName>

<DataType>varchar</DataType>

</Column>

</Columns>

Table Definition Note 5

This will be an XML-formatted list of the collations found using logic similar to the query shown below, along with the counts of each.

SELECT

ISNULL(C.collation_name, 'NULL') AS Collation ,

COUNT(1) AS CountOfCollation

FROM sys.columns AS C

JOIN sys.objects AS O ON C.object_id = O.object_id

WHERE O.type = 'U'

GROUP BY ISNULL(C.collation_name, 'NULL')

FOR XML PATH ('Collation'), ROOT ('Collations');

An example of the format of the XML is below. There will be 1-n Collation elements, one for each entry returned by the query above:

</Collation>

<Name>SQL_Latin1_General_CP1_CI_AS</Name>

</Collation>

</Collations>

Table Definition Note 6

This will be an XML-formatted list of the saved settings in stored procedure definitions.

SELECT

P.name, M.uses_ansi_nulls, M.uses_quoted_identifier, M.is_schema_bound,

M.uses_database_collation, M.is_recompiled, M.null_on_null_input

FROM sys.sql_modules AS M

JOIN sys.procedures AS P ON M.object_id = P.object_id

WHERE P.type = 'P'

AND P.is_ms_shipped = 0

ORDER BY P.name

FOR XML PATH ('Sproc'), ROOT ('Sprocs');

An example of the format of the XML. There will be 1-n Sproc elements, one for each entry returned by the query above:

<Sproc>

<Name>te_abra_hours_worked</Name>

</Sproc>

<Sproc>

<Name>te_accrual_employee_plan</Name>

</Sproc>

</Sprocs>

Table Definition Note 7

This will be an XML-formatted list of tables containing dropped columns where the indexes have not been updated to reflect the new stucture. Documentation of this SQL Server 2012 bug can be found here: https://support.microsoft.com/en-us/kb/3120595?sd=rss&spid=17645

SELECT DISTINCT

OBJECT_NAME(SP.object_id) AS TableWithDroppedCols

FROM sys.system_internals_partition_columns AS SIPC WITH (NOLOCK)

JOIN sys.partitions AS SP WITH (NOLOCK) ON SIPC.partition_id = SP.partition_id

LEFT JOIN sys.columns AS C ON SP.object_id = C.object_id

WHERE SIPC.is_dropped = 1

AND C.system_type_id NOT IN (48, 52, 56, 61, 62, 104, 127, 173)

ORDER BY OBJECT_NAME(SP.object_id)

FOR XML PATH ('TableWithDroppedCols'), ROOT ('TablesWithDroppedCols');

Below is an example of the format of the XML. There will be 1-n “TableWithDroppedCols” elements, one for each record returned by the query above:

<TableWithDroppedCols>ATableName</TableWithDroppedCols>

<TableWithDroppedCols>TableName2</TableWithDroppedCols>

</TablesWithDroppedCols>

Table Definition Note 8

This will be an XML-formatted blob containing the contents of table dbo.product_history. The following query will produce the desired data.

SELECT

history_id, comments, modified_datetime, object_type, version

FROM dbo.product_history

ORDER BY history_id DESC

FOR XML PATH ('entry'), ROOT ('product_history');

Below is an example of the format of the XML. There will be 1-n “entry” elements, one for each record returned by the query above:

<product_history>

<entry>

<history_id>238</history_id>

<comments>Build 003</comments>

<modified_datetime>2016-03-31T16:05:00</modified_datetime>

<object_type>DB</object_type>

</entry>

<entry>

<history_id>237</history_id>

<comments>Build 003</comments>

<modified_datetime>2016-03-31T16:04:00</modified_datetime>

<object_type>DB</object_type>

</entry>

<entry>

<history_id>236</history_id>

<comments>Build 062</comments>

<modified_datetime>2016-03-31T13:41:00</modified_datetime>

<object_type>DB</object_type>

</entry>

</product_history>

Submission Script Dependencies

These are needed only in your development environments. Submission script depends on them. Download these from Project Forum.

dbo.SystemSettings.sql
SubmissionScriptDependencies.sql
Execute BCP Output.sql
Gather Column Data and Parse XML into Rows Without RBAR.sql (this can used directly in submission script or could be used as example for breaking huge xml into chunks of 8k text)
Gather Sproc Data and Parse XML into Rows Without RBAR.sql (this can used directly in submission script or could be used as example for breaking huge xml into chunks of 8k text)

Compilation of SQL Server Properties

Key Information

Challenge Overview

Final Submission Guidelines

LEARN:

ELIGIBLE EVENTS:

REVIEW STYLE:

Final Review:

Approval:

CHALLENGE LINKS:

TOOLBOX:

SHARE:

ID: 30053865