常用又好用的Sql语句

--sql template path
C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
--plugin url
https://www.devart.com/dbforge/sql/sqlcomplete/?utm_campaign=ui_products&utm_source=dbforge_sql_complete_express&utm_medium=about
--column max length
SELECT   TPVID,PartDescription,(LEN(PartDescription)) FROM PUB.tbl_Main_Procurement_GPMATPV WHERE LEN(PartDescription)=100
ORDER BY 3 DESC
--data type convertion
SELECT Cast(LandedSpend as numeric(18,3)) FROM PUB.tbl_Main_Procurement_GPMATPV 


UPDATE PUB.tbl_Main_Procurement_GPMATPV
SET LandedSpend=Cast(LandedSpend as numeric(18,3)) 
WHERE <CONDITION>


DELETE FROM  PUB.tbl_Main_Procurement_GPMATPV 


SELECT Convert(NVARCHAR(50),GETDATE(),120)
SELECT Convert(NUMERIC(18,3),'220.0000')
--common system tables

select object_id,name,schema_name(schema_id),* from sys.objects where type='u' and name like '%temp%' order by 1

select object_id,name,schema_name(schema_id),* from sys.objects where type='u' and name like '%gpma%' order by 1


select object_Name(object_id),* from sys.columns where object_id='297820173'


EXEC sys.sp_rename '_TEST_DEMO.CATEGORY','category','COLUMN'


select
'EXEC sys.sp_rename '+schema_name(obj.schema_id)+'.'+obj.name+'.'+col.name+','''+LOWER(col.name)+''',''COLUMN''',


 schema_name(obj.schema_id),obj.name as tableName,col.name as ColumnName,* from sys.objects obj
inner join sys.columns col
on obj.object_id=col.object_id
where obj.type='U' ---and schema_name(obj.schema_id) in ('pub','stg')
and (col.name like '%HELLO%' OR col.name like '%CATEG%')
order by 1,2,3


--Common system function
sp_spaceUsed 'pub.tbl_Attr_Procurement_CIFSubCategory'
sp_help 'pub.tbl_Attr_Procurement_CIFSubCategory'
sp_helptext  'edp.sp_tch_ExecutionLog' ---store procedure
sp_helptext  'pub.vw_Country' --view


SELECT * FROM SYS.sql_modules WHERE CAST(DEFINITION AS VARCHAR(MAX)) LIKE '%tbl_Attr_Procurement_CIFSubCategory%'


--update column name 
EXEC sys.sp_rename 'dbo._TEST_DEMO.CATEGORY','Category','COLUMN'
EXEC sys.sp_rename 'dbo._TEST_DEMO.hELLO','Hello','COLUMN'
--not null value identify
WHERE COLUMN IS NOT NULL
WHERE ISNULL(COLUMNNAME,'##')<>'##'
WHERE COALESCE(COLUMNNAME1,COLUMNNAME2,'##')<>'##'


SELECT COALESCE(null,1,2)


SELECT COALESCE(null,null,2)
--aggregate. distinct value count
SELECT  (LEN(PartDescription)),Count(1) FROM PUB.tbl_Main_Procurement_GPMATPV  GROUP  BY LEN(PartDescription)
order by 1


select * FROM PUB.tbl_Main_Procurement_GPMATPV where tpvID=-1
select TPVID,
count(  PartDescription) FROM PUB.tbl_Main_Procurement_GPMATPV  GROUP  BY TPVID


--第五行 第十列
SELECT * FROM (
SELECT  ROW_NUMBER() OVER(ORDER BY PartDescription) AS RN,* FROM 
PUB.tbl_Main_Procurement_GPMATPV ) MAIN
WHERE RN=5
for xml path('')


SELECT * FROM SYS.COLUMNS WHERE OBJECT_nAME(object_id)='tbl_Main_Procurement_GPMATPV ' AND column_id=10