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?
Thanks
-
When you say "specification" are you saying you want the DDL for the table itself? – JNK Apr 8 '13 at 15:13
-
The schema of the table of the database that I have to create to pull over the information of the file – jviladrich Apr 8 '13 at 15:54
-
1I don't think so. I think you can infer some of that but there will be no way to get anything about relationships, constraints, datalengths, etc. – JNK Apr 8 '13 at 15:57
-
Well this is my THINKING, not an answer. It may be possible, I'm not a BCP expert. – JNK Apr 8 '13 at 16:54
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.
eg: bcp db_name.schema_name.table_name format nul -T -n -f format-file.fmt
answered Apr 8 '13 at 18:42
-
1I think the OPs problem is that they don't have access to the source table. He wants to infer the schema from the BCP output file, which AFAIK is not possible. – Mark Storey-Smith Apr 8 '13 at 19:18
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.
