sql server script to get table name , column name and data type

sql server table name , column name and datatype




select
@@servername as ServerName,DB_NAME()as databaseName,
schema_name(c.schema_id) as schemaName,c.name as TblName
 ,a.column_id,a.name as ColumnName,b.name as DataType ,a.max_length,case when a.is_identity =1 then 'Yes' end as has_identity

from sys.columns a join sys.types b on a.user_type_id = b.user_type_id
join sys.tables c on a.object_id = c.object_id
order by
1,2,3,4,5

To include rows

create table tempdb..all_tbl_size (name varchar(256),rows int,reserved varchar(90),data varchar(90),index_size varchar(90),unused varchar(90),delta_date datetime default getdate())

EXECUTE sp_MSforeachtable 'insert into tempdb.dbo.all_tbl_size (NAME,ROWS,RESERVED,DATA,INDEX_SIZE,UNUSED) EXECUTE sp_spaceused2 [?]'

select * from tempdb..all_tbl_size



select
@@servername as ServerName,DB_NAME()as databaseName,
schema_name(c.schema_id) as schemaName,c.name as TblName,d.rows
 ,a.name as ColumnName,b.name as DataType ,a.max_length,case when a.is_identity =1 then 'Yes' end
 from sys.columns a join sys.types b on a.system_type_id = b.system_type_id
join sys.tables c on a.object_id = c.object_id
join tempdb..all_tbl_size d on c.object_id = d.name
order by rows desc

For all tables across all the databases 






--- CREATE A EMPTY TEMP TABLE
select
@@servername as ServerName,DB_NAME()as databaseName,
schema_name(c.schema_id) as schemaName,c.name as TblName
 ,a.column_id,a.name as ColumnName,b.name as DataType ,a.max_length,case when a.is_identity =1 then 'Yes' end as has_identity
into ##temp_sch
from sys.columns a join sys.types b on a.user_type_id = b.user_type_id
join sys.tables c on a.object_id = c.object_id

where 1 =0

order by
1,2,3,4,5

GO

-- INSERT THE RESULTS

declare @dbname varchar(90)

declare cur cursor for SELECT name  FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')
open cur
fetch next from cur into @dbname
while @@FETCH_STATUS = 0

begin
print 'GO'
print 'use ['+ @dbname +']'
print
(
' insert
into ##temp_sch
select
@@servername as ServerName,DB_NAME()as databaseName,
schema_name(c.schema_id) as schemaName,c.name as TblName
 ,a.column_id,a.name as ColumnName,b.name as DataType ,a.max_length,case when a.is_identity =1 then ''Yes'' end as has_identity
from sys.columns a join sys.types b on a.user_type_id = b.user_type_id
join sys.tables c on a.object_id = c.object_id

order by
1,2,3,4,5

'
)
fetch next from cur into @dbname
end
close cur
deallocate cur