simple way(s) to get schema info in LINQPad script?

jamesmanning

Context: I wanted to script something that could find all the nullable columns in a SQL Server database (across all tables) and attempt to 'alter table' each of them to change them to not null. with try/catch knowing some will fail - could add the logic to query each nullable column to see if it contains null values, but try/catch is good enough in this context.

The big 'gotcha' is that sql server's 'alter table' syntax to change a column to 'not null' requires restating the column's type - you can't (AFAICT) just alter it to 'not null', but instead it has to be 'int not null' or 'nvarchar(250) not null' or whatever.

I first started looking at querying sys.tables/sys.columns/sys.types, but after running SQL profiler while I did 'script as create table' in SSMS, it appeared that I would be stuck building up the strings for the types (to include maxlength/precision/scale/etc info) which I'd rather avoid doing manually if possible.

My next attempt was seeing if I could get sufficient information in the attributes of the data context that LINQPad generates. That appears to be working fine, but it sure feels like I'm taking the wrong approach and there's likely some other saner/nicer way of getting this kind of info besides digging through the types and their attributes.

What I came up with is in this gist: https://gist.github.com/4391786 (raw version here)

Is there another method of getting the schema info through LINQPad that I just missed?

Thanks!!

sgmoore

The MetaModel Mapping should give you the information.
For example, the following should dump out some info on all the columns on all the tables.

foreach(var table in Mapping.GetTables())
{
(
from dm in table.RowType.DataMembers
where dm.DbType != null
select new { dm.Name , dm.DbType , dm.Type , dm.MappedName, dm.IsPrimaryKey }
)
.Dump(table.TableName);
}

jamesmanning

That's perfect, thanks!

That's what I get for just looking at Mapping in the data grid and not checking what methods were off of it! When I had looked at it, the few properties it had didn't seem to contain any info about the tables, so I started looking elsewhere when I should have looked at its methods to see GetTables()!

Thanks again! This is a vastly better approach.

msGuy

The LINQPad is complaining "The name 'Mapping' does not exist in the current context". Can someone tell me the references or nuget packages that I need to import? Thanks

void Main()
{
foreach (var table in Mapping.GetTables())
{
(
from dm in table.RowType.DataMembers
where dm.DbType != null
select new { dm.Name, dm.DbType, dm.Type, dm.MappedName, dm.IsPrimaryKey }
)
.Dump(table.TableName);
}
}

jamesmanning

@msGuy you just need to make sure linqpad has a normal (linq to sql) connection established and Mapping will be a property off of the UserQuery object.

If your Connection value is set to "None" or some other type of connection, then you won't have it.

msGuy

My database is MySql and driver used is IQ. That is the reason why it is complaining. You are right. I have just tried with MS SQL using linq to sql and it does appear automatically. Looks like there is no support for IQ driver. Thanks.

elias_poulogiannis

One approach here would be as follows.
Remember that MySQL supports ANSI specifications for INFORMATION_SCHEMA. This that you could use the following and get enough schema information for your requirements:

void Main()
{
	var tables = ExecuteQueryDynamic("SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES").Select(table => new
	{
		Schema = table.TABLE_SCHEMA as string,
		Name = table.TABLE_NAME as string,
		Type = table.TABLE_TYPE as string
	}).ToArray();

	foreach (var table in tables.Where(x => x.Type == "BASE TABLE"))
	{
		ExecuteQueryDynamic("SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA={0} AND TABLE_NAME={1}", table.Schema, table.Name).Select(n => new
		{
			Name = n.COLUMN_NAME as string,
			DbType = n.DATA_TYPE as string
		}).Dump();
	}
	//
}

Running the above against any database should retrieve a subset of the results you had in your example. Fetching the private key information will require additional queries obviously but you can get there.