how and where I can find whether a dataset is a file or a table with only the dataset name information in hand. Also would like to know if there is any way in metadata to find which connection a dataset points to.
Depending on which kind of use case (e.g. from inside DSS, like in a Python recipe, or from outside DSS), you may want to use the DSS Public API for this (for instance via its Python client).