Get All Table/ View Details of SQL and GreenPlum

Hi All,

Its me Sujith again,
Today I am going to share the query which will help you to find the Table details in a DB.
Both for SQL and Green Plum

 SQL

SET NOCOUNT ON;

DECLARE @DNNAME TABLE(ID INT IDENTITY,DBNAME VARCHAR(200))

DECLARE @CNT INT,@TEMPDBNAME VARCHAR(200)

INSERT INTO @DNNAME 

select name from master.sys.databases

select @CNT=COUNT(*) from @DNNAME

IF OBJECT_ID('tempdb..#TmpTable') IS NOT NULL DROP TABLE #TmpTable

CREATE TABLE #TmpTable ([Server] VARCHAR(1000),Type VARCHAR(100),DataBaseName VARCHAR(1000),[TABLE] VARCHAR(1000),ATTRIBUTE VARCHAR(1000),DATATYPE VARCHAR(1000))

DECLARE @Sql AS NVARCHAR(4000)

WHILE(@CNT>0)

BEGIN

SELECT @TEMPDBNAME=DBNAME FROM @DNNAME WHERE ID=@CNT

SET @Sql='use [' +@TEMPDBNAME+ '];

insert into  #TmpTable

select ''CTSINTCOSDWD'',''SQL'',DB_NAME() as DataBaseName,a.name [Table],b.name [Attribute],c.name [DataType]

from sysobjects as a

join sys.objects as z on a.id = z.object_id and  z.is_ms_shipped=0

join syscolumns as b on a.id = b.id

join systypes as c on b.xtype = c.xtype 

left join (SELECT  so.id,sc.colid,sc.name 

      FROM    syscolumns sc

      JOIN sysobjects so ON so.id = sc.id

      JOIN sysindexkeys si ON so.id = si.id 

                    and sc.colid = si.colid

      WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid

left join sys.foreign_key_columns as e on a.id = e.parent_object_id and b.colid = e.parent_column_id    

left join sys.objects as g on e.referenced_object_id = g.object_id  

left join sys.extended_properties as h on a.id = h.major_id and b.colid = h.minor_id

where a.type = ''U'' OR a.type=''V'' order by a.name'

SET @CNT=@CNT-1

EXEC (@Sql)

END

SELECT * FROM #TmpTable

Green Plum

SELECT t.table_catalog as ServerName,'Green Plum' as ServerType,t.table_schema as DataBaseName,t.table_name as TableName,column_name as Attribute,data_type as DataType

FROM information_schema.tables t

JOIN information_schema.columns c ON t.table_name = c.table_name