Get Table Names with Column Names and Data Types in SQL Server - SQL Server Tutorial

Sharing buttons:

brother the dummy today we are going to

learn how to get all the tables with

column names and data type from sequel

server database so this is important

let's say we are preparing a mapping

document or for our ETL processor so we

want to get all the tables with column

names and data time datatypes from the

source and then we want to get the same

information from the destination putting

an excel and map them accordingly so

while we might need to some data

conversions from the source to

destination so ETL developers will take

those documents and then prepare the ETL

processor and load the data from source

to the destination but they will have a

guideline and they will have a clear

picture of the data type for data types

from both sides or source as well as

destination so let's go to SSMS and take

a look

sequel server does provide us different

information by using a system tables and

system views so there are some system

views I am going to use in this query so

since that tables this view will provide

us the table names so if I will take a

look I am right now in the sales

database and that I want to get the

table names of so I can use a sistar

tables of system view to get that

information next part of our query your

request is get the columns name so

selects is that columns this system you

is going to return us all the columns so

you can see column names here you can

see the column excuse me IDs you can see

the length you can see a lot more

information you can see you know if this

is this column is the computed column if

this column is used as a Delta or not so

you can see all that information coming

from sis dot columns system view the

next part is we are interested to get

the information for data type so we can

use this card types this system you will

return us that data types so right now

you see that all the

types are listed here and then it tells

us a user type ID system type ID and the

schema and all that so you can take a

detail look on these columns and

whatever the columns you need you can

use in your query now what we learn here

we have three tables we have since the

sorry be user so we have six our table

six dot columns are six dot types they

have all the information and we need to

put a query together join them in a way

so we can get our required information

now let's go to this query I build the

query already so we don't have to worry

right in here so what i'm doing here i'm

using sistar tables and the alias is t

and then excuse me and then I'm getting

a couple of columns are from here so I'm

getting T that name there's a table name

and then I'm getting the schema name and

I use or the schema name function as

schema ID was provided to me in this

table next part is I'm joining on say

start columns up and I need the column

names so excuse me so I'm using a C dot

columns C dot name as the column names

then I need the data types so I'm

joining on assist that types aliases T Y

and I'm joining a since that columns of

table with the since that types in by

using this column from both tables ax

and then and what I'm doing here I'm

excluding all those tables which are

system tables are shipped with the

system so there are Microsoft shipped or

sequel server shipped tables or so I'm

only interested to see what I have

created and then at the end what I'm

doing I'm so over in by the table name

so let me run this query and now what we

see here we can see that the table name

is customer and then we have schema name

and then we have column name and then we

have column data type and if it is

nullable or not death properties here

and then we have a del T if this is used

in identity or not so let's take a look

at this one in call table

employee and the schema dbo the ID is

integer type and it is the identity and

it is not null so that's how you can

take that information and put into your

mapping document there are tons of other

information coming from these system

user so you can run that one and if you

are interested to put that information

such as the created date or modified

date of that table or if it is a I

already used this one if it is a you

want to put that information as well

let's say you get you don't want to

filter these tables but you want to get

everything but you can put that column

here in the Select query so you can see

if it is ms shipped or it is created by

you now you can get all different

information from these columns and use

if it is required to the same goes

versus start columns so you can take a

detail look and whatever makes sense to

you and required by your requirement you

can put in the Select query thanks very

much for watching this video and I'm

going to put these scripts in this

description as well as I will put the

blog link in the description so you can

copy and paste this script and modify

according to your requirement thanks

very much for watching I will see you