--list comlumn comments

--SELECT /*objtype,*/ objname, /*name,*/ value

--FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', 'category', 'column', NULL);

--1 DEQPWeb

--2 DEQPWebSite-Backup

--3 DEQPWebSite

--4 DEQPWebSites-1

--5 DEQPWebSites

--list table comments

/*

SELECT /*objtype,*/ objn ame, /*name,*/ value

FROM fn_listextendedproperty(NULL, 'schema', 'dbo', 'table', NULL, NULL, NULL)

WHERE objname <> 'sysdiagrams' order by objname;

*/

/*

--list all column and type

DECLARE @mytable char(50)

SET @mytable = 'deqp_com_user'

SELECT 

-- SCHEMA_NAME(tb.[schema_id]) AS 'Schema',

-- OBJECT_NAME(tb.[OBJECT_ID]) AS 'Table',

C.NAME as 'Name'

,T.name +

case 

when T.name='decimal' then ' ('+cast(c.precision as varchar(2))+','+cast(c.scale as varchar(2))+')' 

when T.name='nvarchar' and C.max_length=-1 then ' (MAX)'

when T.name='nvarchar' then ' ('+cast(C.max_length/2 as varchar(4))+')'

when T.name='varchar' and C.max_length=-1 then ' (MAX)'

when T.name='varchar' then ' ('+cast(C.max_length as varchar(4))+')'

else ''

end

AS 'Type'

-- ,c.*

-- ,C.is_nullable

-- ,x.objname

,x.value as 'Dictionary'

-- ,'htmlOut += "<label for=\"' + C.name + '\">' + cast(x.value as nvarchar(100)) +': </label><input type=\"text\" name=\"' + C.name + '\" id=\"' + C.name + '\" title=\"' + cast(x.value as nvarchar(100)) + '\" value=\"@r'+C.name+'\" /><br />\n";'  as HTML

-- ,'htmlOut += "<label for=\""+prefix+"' + C.name + '"+postfix+"\">' + cast(x.value as nvarchar(100)) +': </label><input type=\"text\" name=\""+prefix+"' + C.name + '"+postfix+"\" id=\""+prefix+"' + C.name + '"+postfix+"\" title=\"' + cast(x.value as nvarchar(100)) + '\" value=\"\" /><br />\n";'  as HTML

-- ,'<label for="' + C.name + '">' + cast(x.value as nvarchar(100)) +': </label><input type="text" name="' + C.name + '" id="' + C.name + '" title="' + cast(x.value as nvarchar(100)) + '" value="@r'+C.name+'" /><br />'  as HTML

-- ,'r'+ C.name +' = Request["' + C.name + '"] == null?"":Request["'+ C.name +'"].ToString();' as SubmitRead

,'string r'+ C.name +' = "";' as DeclareVar

-- ,'string r'+ C.name +' = Util.GetRequestString("'+C.name+'");' as GetRequestVar

-- ,'<label for="' + C.name + '">' + cast(x.value as nvarchar(100)) +': </label><br/><div id="' + C.name + '">@row.'+C.name+'</div><br />'  as HTML

-- ,'<label for="' + C.name + '">' + cast(x.value as nvarchar(100)) +': </label><br/>@row.'+C.name+'<br />'  as HTML

-- ,'<label for="' + C.name + '">' + cast(x.value as nvarchar(100)) +': </label><br/><label>@row.'+C.name+'</label><br />'  as HTML

-- ,C.name+'=@r'+ C.name +'<br/>' as PrintVar

-- ,C.name+'=@row.'+ C.name +'<br/>' as PrintDBVar

-- ,C.name+',' as ListField

-- ,'r'+C.name+',' as ListRField

,'Util.SafeSqlLiteral(r'+C.name+'),' as ListRFieldSQLSafe

-- ,'$("#'+C.name+'").val("@r'+C.name+'");' as JavaScriptFill

-- ,'r'+C.name+'=(row.'+ C.name +'==null?"":row.'+C.name+'.ToString());' as EditPageForReadDB

  ,'<li><label for="' + C.name + '">' + cast(x.value as nvarchar(100)) +': </label><input type="text" name="' + C.name + '" id="' + C.name + '" title="' + cast(x.value as nvarchar(100)) + '" value="@r'+C.name+'" /></li>'  as FormAddEditWithUL_LI

-- ,C.name + '=@,' as UpdateEdit

FROM SYS.COLUMNS C INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id]

INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id 

inner join fn_listextendedproperty(NULL, 'schema', 'dbo', 'table',@mytable, 'column', NULL) x on x.objname = c.name collate Thai_CI_AS

WHERE tb.[is_ms_shipped] = 0 and OBJECT_NAME(tb.[OBJECT_ID]) = @mytable and x.value <> 'text'

ORDER BY tb.[Name]

*/

--/*

DECLARE @fileName nvarchar(400);

DECLARE @DB_Name nvarchar(50);

SET @DB_Name = 'DEQPWebSites'

SET @fileName = 'V:\900\job\envi_dept\deploy\DEQPWebSites.bak'

BACKUP DATABASE @DB_Name TO DISK = @fileName

--*/

/*

select x.* from

(

select row_number() over (order by post_date desc) as rownum, a.id as id,title,category_id,post_date,abbr_name,name,userid,division_id,approved_center,center_publish,summary,head_pic,attach_file,detail from deqp_news_article a inner join deqp_com_division b on (a.division_id = b.id) where hidden <> '1' and category_id = '1'

) x

where x.rownum between 21 and 30;

*/