how-to copy the meta data of views into xml files per view on mssqlserver

DROP TABLE #ViewsList

SELECT IDENTITY(INT,1,1) AS ID,

Name

INTO #ViewsList

FROM sys.views --Only Views

WHERE name in ( 'view1' , 'view2) )

--WHERE OBJECTPROPERTY(OBJECT_ID(Name),'IsMSShipped') =0

--AND ROUTINE_TYPE='View'

ORDER BY Name

DECLARE

@Loopid INT,

@MaxId INT,

@ViewName VARCHAR(50)

--This is the user that will get the execute/select permissions

SELECT @ViewName = 'SomeUser' --obsolete

--Grab start and end values for the loop

SELECT @Loopid = 1,

@MaxId = MAX(ID)

FROM #ViewsList

DECLARE

@SQL VARCHAR(max),

@CurrentViewName VARCHAR(400) 

--This is where the loop starts

WHILE @Loopid <= @MaxId BEGIN

--grab the function name and type

SELECT @CurrentViewName = Name

FROM #ViewsList

WHERE ID = @Loopid

--construct the statement

SELECT @SQL = 'use db ; SELECT   Cols.TABLE_SCHEMA,  Cols.TABLE_NAME, Cols.COLUMN_NAME, Cols.ORDINAL_POSITION, Cols.DATA_TYPE, Cols.NUMERIC_PRECISION, Cols.NUMERIC_SCALE,  Cols.IS_NULLABLE, Cols.CHARACTER_MAXIMUM_LENGTH,  COLUMNPROPERTY(object_id(Cols.TABLE_NAME), Cols.COLUMN_NAME, ''IsIdentity'') AS IsIdentity, ( SELECT COUNT(KCU.COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.TABLE_NAME = TC.TABLE_NAME AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = ''PRIMARY KEY'' WHERE KCU.TABLE_NAME = Cols.TABLE_NAME AND KCU.COLUMN_NAME = Cols.COLUMN_NAME ) AS IsIndex FROM [INFORMATION_SCHEMA].[COLUMNS] Cols where Table_Name = ''' + @CurrentViewName + '''  AND Cols.TABLE_SCHEMA = ''schemaName'' ORDER BY Cols.TABLE_NAME, Cols.COLUMN_NAME , Cols.table_schema , Cols.ORDINAL_POSITION'

--exec (@SQL) --Action !!!

--print @sql

--increment counter

declare @bcpsql varchar(8000)

select @bcpsql = 'BCP "' + @sql+ '" queryout D:\data\dbName\xml\' + @CurrentViewName + '.xml -c -t, -T '

exec master..xp_cmdshell @bcpsql

--print @bcpsql

SET @Loopid = @Loopid + 1

END --eof while

--clean up

DROP TABLE #ViewsList