I have a file saved using BCP and I want to know the specification of the table of the database that it needs to be materialized on the database. Is that possible?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You can do this using the BCP format parameter. This will create a format file of the table for you.
Based on your requirement, you can create XML/native/character formatted files.
bcp db_name.schema_name.table_name format nul -T -n -f format-file.fmt
Well we tend not to care what the originators table structure is, but only if it meets our requirements (which we send to them).
If you are trying to figure out how to design a way to store the data permanently because you don't currently have a structure, then this is the method I use. Import the file into a staging table (not the final permanent table, I highly recommend you do that anyway, so you can clean the data before moving it to its final location) that has everything defined as varchar(max) or nvarchar(max). Now you can examine each field and see what is in in, look for the max lentgth of each field, check to see if numeric fields contain only numbers etc. Then you will know from the data what types of fields you need in the production table. Since this is only the first file, I would tend to create my final table with slightly larger fields than the data indicates, so it doesn't fail on the second file you get. After you have some history, you can tighten them.
If you use SSIS instead of bcp, there is a data profiling task you can use to see what the data is really like.