Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

C# DataTable (in memory) vs SQL Lookups

Avatar of nightshadz
nightshadzFlag for United States of America asked on
DatabasesC#SQL
4 Comments1 Solution760 ViewsLast Modified:
Looking for some best practice advice on a task I'm working on. The tools I'm using are Visual Studio 2015 (C#) and SQL.

I need to look up the data types of every column in every table of a source database using the INFORMATION_SCHEMA.COLUMNS view of an SQL DB and then create that column with the same data type in a new database. In addition to this, there will be other columns created that do not exist in the source db so it's not as simple as using the create table script feature from within the SQL management studio. My concern is there's 100s of tables and having to query the column data type from the source db and create it in the new db will not be the most efficient method.

I was considering using the below query to pull out everything I'd need into a DataSet and use that as my lookup, but I'm curious if there are any suggestions on how to improve this approach and if I missing any critical information in the below query? We're not concerned with PKs and FKs for this project.

SELECT TABLE_NAME,
	    COLUMN_NAME,
		IS_NULLABLE,
		DATA_TYPE,
		CHARACTER_MAXIMUM_LENGTH,
		NUMERIC_PRECISION,
		NUMERIC_SCALE,
		CHARACTER_SET_NAME,
		COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_NAME ASC, COLUMN_NAME ASC
ASKER CERTIFIED SOLUTION
Avatar of zephyr_hex (Megan)
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answers