This should get you started I think, the code is in C# but you can use the query part in SQL if you wish:
var tableName = "MyTable";
var schema = "dbo";
var command = msConnection.CreateCommand();
var msSql = string.Format(@"SELECT col.name, is_rowguidcol, is_identity
FROM sys.indexes ind
INNER JOIN sys.index_columns ic
ON ind.object_id = ic.object_id
AND ind.index_id = ic.index_id
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
INNER JOIN sys.tables t
ON ind.object_id = t.object_id
WHERE t.is_ms_shipped = 0
AND (col.is_rowguidcol > 0 OR col.is_identity > 0)
AND OBJECT_SCHEMA_NAME(ind.object_id) = '{0}'
AND OBJECT_NAME(ind.object_id) = '{1}'
", schema, tableName);
command.CommandText = msSql;
using (var dr = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (dr.Read())
{
object[] rowObjects = new object[3];
dr.GetValues(rowObjects);
var columnName = rowObjects[0].ToString().ToLower();
var isrowguidcol = rowObjects[1].ToString();
var isidentity = rowObjects[2].ToString();
if (!string.IsNullOrEmpty(columnName))
{
if (isrowguidcol == "True")
{
Debug.Print("ROWGUIDCOL " + tableName + " " + columnName);
}
else if (isidentity == "True")
{
Debug.Print("IDENTITY " + tableName + " " + columnName);
}
}
}
}