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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.