get table listed above n number of rows

 DECLARE @TableName varchar(max)=NULL

SELECT @TableName=COALESCE(@TableName+',','')+t.TABLE_CATALOG+'.'+ t.TABLE_SCHEMA+'.'+o.Name

  FROM sysindexes AS i

  INNER JOIN sysobjects AS o ON i.id = o.id

  INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME=o.name

 WHERE i.indid < 2

  --AND OBJECTPROPERTY(o.id,'IsMSShipped') = 0

  AND i.rowcnt >5

  --AND o.xtype !='TF'

 ORDER BY o.name ASC


 print @tablename




Comments

Post a Comment

Popular posts from this blog

unpivot in sql server above version 2016

Image
 Declare @YourTable Table ([ID] varchar(50),[Col1] varchar(50),[Col2] varchar(50)) Insert Into @YourTable Values   (1,'A','B') ,(2,'R','C') ,(3,'X','D') Select A.[ID]       ,Item  = B.[Key]       ,Value = B.[Value]  From  @YourTable A  Cross Apply ( Select *                  From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper ))                  Where [Key] not in ('ID','Other','Columns','ToExclude')              ) B
Read more

UNPIVOT in sql server

Image
SELECT id         ,entityId         ,indicatorname         ,indicatorvalue   FROM (VALUES         (1, 1, 'Value of Indicator 1 for entity 1', 'Value of Indicator 2 for entity 1', 'Value of Indicator 3 for entity 1'),         (2, 1, 'Value of Indicator 1 for entity 2', 'Value of Indicator 2 for entity 2', 'Value of Indicator 3 for entity 2'),         (3, 1, 'Value of Indicator 1 for entity 3', 'Value of Indicator 2 for entity 3', 'Value of Indicator 3 for entity 3'),         (4, 2, 'Value of Indicator 1 for entity 4', 'Value of Indicator 2 for entity 4', 'Value of Indicator 3 for entity 4')        ) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3) UNPIVOT (     indicatorvalue     FOR indicatorname IN (Indicator1, Indicator2, Indicator3) ) UNPIV;  
Read more