RSS

Monthly Archives: April 2011

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