TSQL List all Columns in a Database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Declare @Table_Name VarChar(100) ,@Column_Name VarChar(100)
Set @Table_Name = ''
Set @Column_Name = ''

Select
RowNumber = Row_Number() Over( PARTITION BY T.[Name] Order By T.[Name],C.column_id  ),
SCHEMA_NAME( T.schema_id ) As SchemaName ,  
T.[Name] As Table_Name ,
C.[Name] As Field_Name ,
sysType.name ,
C.max_length , C.is_nullable , C.is_identity , C.scale , C.precision  
From Sys.Tables As T
Left Join Sys.Columns As C On ( T.[Object_Id] = C.[Object_Id] )
Left Join sys.types As sysType On ( C.user_type_id = sysType.user_type_id )
Where ( Type = 'U' )
    And ( C.Name Like '%' + @Column_Name + '%' )  
    And ( T.Name Like '%' + @Table_Name + '%' )

Leave a Reply