troubleshooting Question

C# DataTable (in memory) vs SQL Lookups

Avatar of nightshadz
nightshadzFlag for United States of America asked on
DatabasesSQLC#
4 Comments2 Solutions760 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
Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros