--/* code is generated on the basis of geralization method

-- you can check by taking as much as column you want and check like

-- say suppose i am taking 9 columns where i am inserting * it is printing Pyramid like

-- but while running following code select Result to text

-- * * * * * * * * *

-- * * * * * * *

-- * * * * *

-- * * *

-- * *

-- *

--*/

IF(EXISTS(SELECT OBJECT_ID('Astrick')))

DROP TABLE Astrick

CREATE TABLE Astrick

(

id INT IDENTITY(1,1)

,C1 VARCHAR(10)

,C2 VARCHAR(10)

,C3 VARCHAR(10)

,C4 VARCHAR(10)

,C5 VARCHAR(10)

,C6 VARCHAR(10)

,C7 VARCHAR(10)

,C8 VARCHAR(10)

,C9 VARCHAR(10)

)

DECLARE @i INT,@i1 INT,@HOLD INT,@i2 INT,@HOLD2 INT,@loop INT

DECLARE @INSERT VARCHAR(MAX) -- column name of insert query

DECLARE @Insert_Query VARCHAR(MAX) --- Insert query

DECLARE @VALUE VARCHAR(MAX) --- value part of insert query

SET @I1=1

SELECT @I2=COUNT(*) FROM sys.columns S WITH(NOLOCK)

JOIN sys.all_objects O WITH(NOLOCK) ON S.object_id=O.object_id

WHERE O.name LIKE 'Astrick' and collation_name is not null

SET @loop = @i2

SET @i = @i2

SET @INSERT=''

SET @VALUE=''

WHILE(@i >0)

BEGIN

SET @HOLD = @I1

SET @HOLD2 = @I2

WHILE(@I1 <=@I2)

BEGIN

IF(@I1=@I2)

BEGIN

SET @INSERT = @INSERT + 'C'+CONVERT(VARCHAR(10),@I1)

SET @VALUE=@VALUE +''' *'''

END

ELSE

BEGIN

SET @INSERT=@INSERT + 'C'+CONVERT(VARCHAR(10),@I1)+','

SET @VALUE =@VALUE + ''' *'','

END

SET @I1 = @I1 +1

END

SET @Insert_Query = 'INSERT INTO Astrick('+@INSERT+') VALUES('+@VALUE+')'

EXECUTE(@Insert_Query)

SET @i = @i -2

SET @I1 = @HOLD + 1

SET @I2 = @HOLD2 -1

SET @INSERT='' -- resetting string to blank to use it agin in the same loop

SET @VALUE=''

END

--- code to print Asteric table

SET NOCOUNT ON

DECLARE @Asterik_Code VARCHAR(MAX),@Select_Code VARCHAR(MAX)

DECLARE @c INT

SET @c=1

SET @Asterik_Code =''

WHILE(@c<=@loop)

BEGIN

IF(@c=@loop)

BEGIN

SET @Asterik_Code=@Asterik_Code+ 'isnull('+ ( SELECT a.name FROM (

SELECT s.name,ROW_NUMBER() OVER(ORDER BY s.name) id FROM sys.columns S WITH(NOLOCK)

JOIN sys.all_objects O WITH(NOLOCK) ON S.object_id=O.object_id

WHERE O.name LIKE 'Astrick' and collation_name is not null) a WHERE a.id=@c) +','''')'

END

ELSE

BEGIN

SET @Asterik_Code=@Asterik_Code+ 'isnull('+ ( SELECT a.name FROM (

SELECT s.name,ROW_NUMBER() OVER(ORDER BY s.name) id FROM sys.columns S WITH(NOLOCK)

JOIN sys.all_objects O WITH(NOLOCK) ON S.object_id=O.object_id

WHERE O.name LIKE 'Astrick' and collation_name is not null) a WHERE a.id=@c) +',''''),'

END

SET @c = @c + 1

END

SET @Select_Code = 'SELECT '+@Asterik_Code +' FROM Astrick'

EXECUTE(@Select_Code)