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