In this post ,we will generate the script that will return us all the table names with column names and data type of those columns. We often need this information. Let's say we are going to prepare mapping document for load the data from Source Database to Destination database. We can get the list of all tables with column names and data types from Source Database and Destination Database and then paste in Excel and map the required input columns to output columns for ETL Process.
SELECT T.Name AS TableName,
Schema_name(T.schema_id) AS SchemaName,
C.Name AS ColumnName,
Ty.Name AS ColumnDataType,
C.is_nullable AS IsNullAble,
C.is_identity AS IsIdentity
FROM sys.tables T
INNER JOIN sys.columns C
ON T.OBJECT_ID = C.OBJECT_ID
INNER JOIN sys.types Ty
ON C.system_type_id = Ty.system_type_id
WHERE T.is_ms_shipped = 0
ORDER BY T.name
SELECT T.Name AS TableName,
Schema_name(T.schema_id) AS SchemaName,
C.Name AS ColumnName,
Ty.Name AS ColumnDataType,
C.is_nullable AS IsNullAble,
C.is_identity AS IsIdentity
FROM sys.tables T
INNER JOIN sys.columns C
ON T.OBJECT_ID = C.OBJECT_ID
INNER JOIN sys.types Ty
ON C.system_type_id = Ty.system_type_id
WHERE T.is_ms_shipped = 0
ORDER BY T.name
Fig 1: Get List of Tables with Column Names and Data Types from SQL Server Database
Thanks a ton, this was exactly what I needed!
ReplyDeleteSELECT * FROM INFORMATION_SCHEMA.COLUMNS
DeleteVery useful post..
ReplyDeleteThe 1000 client base is your preparation datasets and with that current arrangement of information, you have to make, manufacture and train a model that can anticipate this specific viewpoint about your clients.Data Analytics Courses
ReplyDeleteVery nice job... Thanks for sharing this amazing and educative blog post! ExcelR Data Analytics Courses
ReplyDeleteKnow more about Data Analytics
ReplyDeleteI am genuinely thankful to the holder of this web page who has shared this wonderful paragraph at at this place
Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
ReplyDeleteSimple Linear Regression
Correlation vs covariance
data science interview questions
KNN Algorithm
Logistic Regression explained
This is my first time visit here. From the tons of comments on your articles.I guess I am not only one having all the enjoyment right here! ExcelR Data Analytics Courses In Pune
ReplyDeletevery well explained .I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
ReplyDeleteSimple Linear Regression
Correlation vs covariance
data science interview questions
KNN Algorithm
Logistic Regression explained
Great post, and great website. Thanks for the information! https://www.vanityliving.com/collections/round-glass-dining-tables-dubai-uae
ReplyDeleteNew web site is looking good. Thanks for the great effort. Affordable CRM
ReplyDeleteJust what I need! A course that would help me and my business know what is actually important in coming up with brand names. Would definitely recommend this! read more
ReplyDeleteThis is really a nice and informative, containing all information and also has a great impact on the new technology. Check it out here:Tables
ReplyDeleteI have been searching to find a comfort or effective procedure to complete this process and I think this is the most suitable way to do it effectively. ExcelR Data Analyst Course
ReplyDeleteThis is my first visit to your web journal! We are a group of volunteers and new activities in the same specialty. Website gave us helpful data to work. 増田裕介
ReplyDeleteHow would I edit this code to exclude the empty Tables (ie Tables that have columns, but no rows/records)?
ReplyDeleteI recently came across your article and have been reading along. I want to express my admiration of your writing skill and ability to make readers read from the beginning to the end.
ReplyDeleteData Analytics Courses In Pune
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ bao nhiêu tiền
vé máy bay từ los về việt nam
từ nhật bản về việt nam bao nhiêu tiền
ve may bay tư duc ve viet nam
khi nào có chuyến bay từ canada về việt nam
ve may bay tu han quoc ve viet nam
vé máy bay tết 2022 giá rẻ
Though space is limited, one of the best parts about city life is having people over for a good time. transformer table
ReplyDelete