Yes - INFORMATION_SCHEMA.TABLES
should be what you're looking for.
It will give you something like:
aspnetdb dbo aspnet_Paths BASE TABLE
aspnetdb dbo aspnet_PersonalizationAllUsers BASE TABLE
aspnetdb dbo aspnet_PersonalizationPerUser BASE TABLE
aspnetdb dbo vw_aspnet_WebPartState_Paths VIEW
aspnetdb dbo vw_aspnet_WebPartState_Shared VIEW
aspnetdb dbo vw_aspnet_WebPartState_User VIEW
aspnetdb dbo aspnet_Applications BASE TABLE
If you want to know about the columns of a table, look at INFORMATION_SCHEMA.COLUMNS
.
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'aspnet_Users'
will give you all the details for the columns for the table specified.