#region SVN File History
// ***********************************************************
// Copyright � 2012 Craig Greenock.
// Contact - cgreenock@bcs.org.uk
// Version 0.0 - 22 January 2012 20:38
// Machine - KEFALLONIA Microsoft Windows NT 6.1.7600.0
//
//
//
// $Author: cig $
// $HeadURL: https://kefallonia/svn/Projects_2008/Schema/trunk/Schema/TSQL/SQL.cs $
// $LastChangedBy: cig $
// $LastChangedDate: 2012-02-12 20:40:54 +0000 (Sun, 12 Feb 2012) $
// $Revision: 365 $
//
// ***********************************************************
#endregion // SVN File History
#region References
using System;
using System.Data.SqlClient;
using Schema.Constants;
#endregion // References
namespace Schema.TSQL
{
/// <summary>
/// Return T-SQL queries
/// </summary>
internal class Query
{
// Don't allow instances of this class.
private Query(){}
/// <summary>
/// Retrieve summary schemata for all tables where names match includePattern.
/// </summary>
/// <param name="includePattern">If empty treated as a request to retrieve all tables.</param>
/// <returns></returns>
public static string GetSchemata(string includePattern, bool includeHidden)
{
string allowHidden = "0";
if (includeHidden)
allowHidden = "%";
if (string.IsNullOrEmpty(includePattern))
includePattern = "%";
string query = @"
select
[table].table_name as [Name],
case [table].table_type
when 'VIEW' then 1
else 0 end
as [IsView],
[tableProps].[comment] as [Comment],
[tableProps].[hidden] as [Hidden],
[tableProps].[version] as [Version],
[column].column_name as [Name],
case
when [keycol].constraint_name is null then 0
else 1 end
as [PrimaryKey],
[column].data_type as [Type],
case [column].data_type
when 'varchar' then [column].character_maximum_length
when 'char' then [column].character_maximum_length
when 'nvarchar' then [column].character_maximum_length
when 'nchar' then [column].character_maximum_length
else (select length from [systypes] where name = [column].data_type)
end
as [Length],
case [column].is_nullable
when 'No' then 0
else 1 end
as [AllowNull],
left(substring([column].column_default,2,
len([column].column_default)-2),255)
as [DefaultValue],
[columnProps].[comment] as [Comment],
[columnProps].[version] as [Version]
from
information_schema.tables [table]
inner join (select
[name], object_id
from sys.tables
union
select
[name], object_id
from sys.views
) as systable
on systable.name = [table].table_name
-- Pivot extended properties for the table.
left join
(select
major_id,
max(case when [name]='comment' then [value] else '' end) as comment,
max(case when [name]='hidden' then [value] else '0' end) as hidden,
max(case when [name]='version' then [value] else '' end) as version
from sys.extended_properties
where class = 1 and minor_id = 0
group by major_id
) as [tableProps]
on [tableProps].major_id = [systable].object_id
inner join Information_schema.Columns [column]
on [table].table_name = [column].table_name
and [table].table_name like '{0}'
left join Information_schema.table_constraints [constraint]
on [constraint].table_name = [column].table_name
and [constraint].constraint_type='PRIMARY KEY'
left join Information_schema.Key_Column_Usage as [keycol]
on [keycol].constraint_name = [constraint].constraint_name
and [keycol].column_name = [column].column_name
-- Pivot extended properties for the column.
left join
(select
major_id, minor_id,
max(case when [name]='comment' then [value] else '' end) as comment,
max(case when [name]='version' then [value] else '' end) as version
from sys.extended_properties
where class = 1
group by major_id, minor_id
) as [columnProps]
on [columnProps].major_id = [systable].object_id
and [columnProps].minor_id = [column].[ordinal_position]
where coalesce(cast([tableProps].[hidden] as varchar(2)), '0') like '{1}'
order by
[column].table_name asc,
[column].ordinal_position asc
FOR XML AUTO";
query =string.Format(query, includePattern, allowHidden);
return query;
}
/// <summary>
/// Obtain a list of columns and their descriptions for a single table.
/// </summary>
/// <param name="tableName"></param>
/// <param name="dataDictionary"></param>
/// <returns>SQL query as a string</returns>
public static string GetSingleTable(string tableName)
{
return GetSchemata(tableName, true);
}
/// <summary>
/// Straightforward list of all columns in a table.
/// </summary>
/// <param name="?"></param>
/// <returns></returns>
public static string ColumnList(string tableName)
{
return string.Format(@"select [column_name] as [name] from information_schema.columns
where [table_name] = '{0}'
order by [ordinal_position]", tableName);
}
/// <summary>
/// Add or update a single table level extended property.
/// </summary>
/// <param name="tableName"></param>
/// <param name="propertyName"></param>
/// <param name="propertyValue"></param>
/// <returns></returns>
public static string UpdateTableProperty(string tableName,
string propertyName,
string propertyValue)
{
string update = @"
declare @tableName varchar(127)
declare @propertyName varchar(127)
declare @propertyValue varchar(2048)
declare @objectType varchar(10)
set @tableName = '{0}'
set @propertyName = '{1}'
set @propertyValue = '{2}'
set @objectType = 'TABLE'
if 1 = (select count([table_name]) as [found] from information_schema.views
where [table_name] = @tableName)
begin
set @objectType = 'VIEW'
end
if 1 =( select count([tableProps].[value]) as [found] from
information_schema.tables as [table]
inner join (select
[name], object_id
from sys.tables
union
select
[name], object_id
from sys.views
) as systable
on [table].[table_name] = @tableName
and systable.name = [table].table_name
inner join sys.extended_properties as tableProps
on [tableProps].[class] = 1
and [tableProps].[major_id] = [systable].object_id
and [tableProps].[minor_id] = 0
and [tableProps].[name] = @propertyName )
begin
EXEC sys.sp_updateextendedproperty
@name = @propertyName,
@value = @propertyValue,
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = @objectType, @level1name = @tableName;
end
else
begin
EXEC sys.sp_addextendedproperty
@name = @propertyName,
@value = @propertyValue,
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = @objectType, @level1name = @tableName;
end";
update = string.Format(update, tableName, propertyName, propertyValue);
return update;
}
/// <summary>
/// Add or update a single extended property for a table column.
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <param name="propertyName"></param>
/// <param name="propertyValue"></param>
/// <returns></returns>
public static string UpdateColumnProperty(string tableName,
string columnName,
string propertyName,
string propertyValue)
{
string update = @"
declare @tableName varchar(127)
declare @columnName varchar(127)
declare @propertyName varchar(127)
declare @propertyValue varchar(2048)
declare @objectType varchar(10)
set @tableName = '{0}'
set @columnName = '{1}'
set @propertyName = '{2}'
set @propertyValue = '{3}'
set @objectType = 'TABLE'
if 1 = (select count([table_name]) as [found] from information_schema.views
where [table_name] = @tableName)
begin
set @objectType = 'VIEW'
end
if 1 =( select count([columnProps].[value]) as [found] from
information_schema.columns as [table]
inner join (select
[name], object_id
from sys.tables
union
select
[name], object_id
from sys.views
) as systable
on [table].[table_name] = @tableName
and [table].[column_name] = @columnName
and [systable].[name] = [table].table_name
inner join sys.columns as [syscolumn]
on [syscolumn].[object_id] = [systable].[object_id]
and [syscolumn].[name] = @columnName
inner join sys.extended_properties as [columnProps]
on [columnProps].[class] = 1
and [columnProps].[major_id] = [systable].[object_id]
and [columnProps].[minor_id] = [syscolumn].[column_id]
and [columnProps].[name] = @propertyName )
begin
EXEC sys.sp_updateextendedproperty
@name = @propertyName,
@value = @propertyValue,
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = @objectType, @level1name = @tableName,
@level2type = N'COLUMN', @level2name = @columnName;
end
else
begin
EXEC sys.sp_addextendedproperty
@name = @propertyName,
@value = @propertyValue,
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = @objectType, @level1name = @tableName,
@level2type = N'COLUMN', @level2name = @columnName;
end";
update = string.Format(update, tableName, columnName, propertyName, propertyValue);
return update;
}
}
} // end namespace
Submit an article or tip