RSS

MS SQL rename column

exec sp_rename ‘tableName.ColumnName’, ‘newColumnName’, ‘COLUMN’

EXEC sp_rename 'LeadTracking.cSource', 'cSourceLookupId', 'COLUMN'

Original source

http://dotnetcoderoom.wordpress.com/2008/09/02/rename-a-table-column-in-ms-sql/

Advertisements
 
Leave a comment

Posted by on May 19, 2011 in SQL

 

Tags:

Get all columns of a certain type from database

How do you get all the tables and columns in your database where the type is like money?

select
     o.name as tableName,
    c.name as ColumnName,
    t.Name as ColumnType,
    o.type as ObjectType,
    u.name as SchemaName
from syscolumns c
inner join sysobjects o
    on o.id=c.id
inner join sysusers u
    on u.uid= o.uid
inner join sys.types t
    on t.system_type_id = c.xtype
where t.Name like '%money%'

 
Leave a comment

Posted by on April 12, 2011 in SQL

 

Tags:

Extract yield lines to common method

Recently I wanted to refactor out some common lines out of our auto generated code and our hand written code.

The common lines of code were:

if (obj.IsNull()) yield break;
foreach (var m in obj)
{
	m.AcceptChanges();
	yield return m;
}

An example of a method to be refactored is

public IEnumerable<MyModels.StatusLookup> GetAll()
{
	var results = Database.Current.pStatusLookupLoadAll()
		.ExecuteTypedList<MyModels.StatusLookup>();
	if (results.IsNull())
		yield break;
	
	foreach (var m in results)
	{
		//..Common logic lines...  //..Common logic lines...
		yield return m;
	}
}

My attempts at creating an IEnumerable extension method were failing so I asked my question on stackoverflow. And viola my question was answered.

http://stackoverflow.com/questions/5557019/extract-yield-lines-into-common-method/5561020#comment-6342765

Here is the proper extension method

// Note: We are using a generic type constraint on T.
public static IEnumerable<T> AcceptChangesAndYield<T>
	(this IEnumerable<T> obj)
	where T : MyModels.Interfaces.ILookup
{
	if (obj.IsNull())
		yield break;
	foreach (var m in obj)
	{
		//Put custom code here above the yield
		yield return m;
	}
}
 
Leave a comment

Posted by on April 7, 2011 in .Net, C#, Extension

 

Tags: , ,

Get your Foreign Keys

How do you query your ms sql database and find out what columns in your table are foreign keys?

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER],

C.TABLE_SCHEMA [PKTABLE_OWNER],

C.TABLE_NAME [PKTABLE_NAME],

KCU.COLUMN_NAME [PKCOLUMN_NAME],

C2.TABLE_CATALOG [FKTABLE_QUALIFIER],

C2.TABLE_SCHEMA [FKTABLE_OWNER],

C2.TABLE_NAME [FKTABLE_NAME],

KCU2.COLUMN_NAME [FKCOLUMN_NAME],

RC.UPDATE_RULE,

RC.DELETE_RULE,

C.CONSTRAINT_NAME [FK_NAME],

C2.CONSTRAINT_NAME [PK_NAME],

CAST(7 AS SMALLINT) [DEFERRABILITY]

FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU

ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA

AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA

AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2

ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA

AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2

ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA

AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME

AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION

WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'

and

C.TABLE_NAME ='TimeSpan'
 
Leave a comment

Posted by on April 5, 2011 in SQL

 

Enumeration GetStringValue

First create an attribute to use on the enumeration’s values.

/// <summary>
/// This attribute is used to represent a string value for a value in an enum.
/// </summary>
/// <threadsafety>This class is mutable and not threadsafe.</threadsafety>
// Credit to: http://www.codeproject.com/KB/cs/stringenum.aspx
[AttributeUsage(AttributeTargets.Field)]
public class StringValueAttribute : Attribute
{
/// <summary>
/// Holds the string value for a value in an enum.
/// </summary>
/// <value>the string value</value>
public string StringValue { get; protected set; }

/// <summary>
/// Constructor used to init a StringValue Attribute.
/// </summary>
/// <param name="value">the string value</param>
public StringValueAttribute(string value)
{
this.StringValue = value;
}
}

Next create an extension method to easily get the string value from the enumeration. Note: This code was downloaded from somewhere but I cannot find where I got it.

/// <summary>
/// This entensions class adds the GetStringValue method for enum to retrieve its string representation.
/// </summary>
/// <threadsafety>This class is stateless and thread safe.</threadsafety>
public static class Extensions
{
/// <summary>
/// This will get the string value for a given enums value, this will
/// only work if you assign the StringValue attribute to
/// the items in your enum.
/// </summary>
/// <param name="value">the enum</param>
/// <returns>the string value</returns>
public static string GetStringValue(this Enum value)
{
// get the type
Type type = value.GetType();

// get fieldinfo for this type
FieldInfo fieldInfo = type.GetField(value.ToString());

// get the stringvalue attributes
StringValueAttribute[] attribs = fieldInfo.GetCustomAttributes(
typeof(StringValueAttribute), false) as StringValueAttribute[];

// return the first if there was a match
return attribs.Length > 0 ? attribs[0].StringValue : null;
}
}
 
Leave a comment

Posted by on March 29, 2011 in .Net, C#, Extension

 

Why am am I blogging?

To be honest it is for selfish reasons. I have found over the years that I will read and search for code that I find interesting and then later, perhaps on another code project, I cannot find that interesting or cool post.

 
Leave a comment

Posted by on March 29, 2011 in Uncategorized