private static string GetSqlColumnsForTable(string tableName)
{
var sb = new StringBuilder();
sb.AppendLine("SELECT DISTINCT");
sb.AppendLine(" colorder,");
sb.AppendLine(" syscolumns.name,");
sb.AppendLine(" case when primaryKey.xtype ='PK' then 'true' else 'false' end as isPrimaryKey, ");
sb.AppendLine(" case when fk.fkey is null then 'false' else 'true' end as isForeignKey,");
sb.AppendLine(" systypes.name as datatype,");
sb.AppendLine(" syscolumns.length,");
sb.AppendLine(" case when syscolumns.isnullable = 0 then 'false' else 'true' end as allowNull,");
sb.AppendLine(" case when syscomments.text is null then '' else SUBSTRING ( syscomments.text , 2 , len(syscomments.text)-2 ) end as defaultValue,");
sb.AppendLine(" case when syscolumns.autoval is null then 'false' else 'true' end as isIdentity");
sb.AppendLine("FROM");
sb.AppendLine(" sysobjects");
sb.AppendLine(" inner join syscolumns on syscolumns.id = sysobjects.id");
sb.AppendLine(" inner join systypes on systypes.xtype = syscolumns.xtype");
sb.AppendLine(" left outer join sysindexkeys on sysindexkeys.id = syscolumns.id AND sysindexkeys.colid = syscolumns.colid");
sb.AppendLine(" left outer join sys.indexes pk on pk.id = sysindexkeys.id AND pk.indid = sysindexkeys.indid");
sb.AppendLine(" left outer join sysobjects primaryKey on pk.name = primaryKey.name");
sb.AppendLine(" left outer join sysforeignkeys fk on fk.fkeyid = syscolumns.id AND fk.fkey = syscolumns.colorder");
sb.AppendLine(" left outer join sys.default_constraints ON syscolumns.cdefault = sys.default_constraints.parent_column_id ");
sb.AppendLine("WHERE");
sb.AppendLine(" sysobjects.name = '" + tableName + "' AND systypes.name <> 'sysname' order by [colorder]\n");
return sb.ToString();
}
