Post by Goborijung at 2022-04-06 06:37:19 | ID: 1449
-- การ Add Columns ใหม่เข้าไปใน Table
Syntax :
ALTER TABLE table_name ADD column_name datatype
ตัวอย่าง :
ALTER TABLE CUSTOMERS ADD SEX char(1)
Post by Goborijung at 2022-04-06 06:18:00 | ID: 1447
-- การ Backup แบบเอาแค่โครงสร้าง Database ไม่เอา Data
1. เลือก Database Name
2. Right Click > Tasks > Generate Script...
3. เลือกหัวข้อ Script entire database and all database objects
4. เลือก Save as script file
5. เลือก Location Save ตั้งชื่อ FileName.sql
6. Next > Next > รอให้มันสีเขียวทั้งหมด
7. กด Finish เป็นอันเสร็จเรียบร้อยจ้า
Post by Goborijung at 2022-04-06 06:28:08 | ID: 1448
-- การ Backup Table ใน Database แบบเอา Data ด้วย
1. เลือก Database Name
2. Right Click > Tasks > Generate Script...
3. เลือกหัวข้อ Select specific database objects > เลือก Table Name
4. Click ปุ่ม Advanced > หัวข้อ General เลือกเป็น Types of data to script
5. เปลี่ยนจาก Schema only ให้เป็น Schema and data
6. เลือก Save as script file
7. เลือก Location Save ตั้งชื่อ FileName.sql
8. Next > Next > รอให้มันสีเขียวทั้งหมด
9. กด Finish เป็นอันเสร็จเรียบร้อยจ้า
Post by Goborijung at 2022-03-08 08:40:57 | ID: 1402
https://www.thaicreate.com/community/sqlserver-linked-server.html หรือ ใช้คำสั่ง Sql เช่น USE [master] GO /****** Object: LinkedServer [191.20.2.xx] Script Date: 13/12/2566 10:42:00 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'191.20.2.xx', @srvproduct=N'SQL Server' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'191.20.2.xx',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='5555' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'191.20.2.xx', @optname=N'remote proc transaction promotion', @optvalue=N'false' GO
Post by Goborijung at 2022-06-09 11:08:37 | ID: 1590
>> How to Create Stored Procedure Syntax :CREATE PROCEDURE procedure_name AS sql_statement GO;---------------- Example :CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO; การใช้งาน : EXEC SelectAllCustomers;---------------- Stored Procedure With One Parameter :CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) AS SELECT * FROM Customers WHERE City = @City GO; การใช้งาน : EXEC SelectAllCustomers @City = 'London';---------------- Stored Procedure With Multiple Parameters :CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO; การใช้งาน : EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';
Post by Goborijung at 2023-07-12 09:48:56 | ID: 1949
Declare @tblName varchar(100) = 'PDK1_M_PAOUNDAM'-- หา Index
exec sp_helpindex @tblName -- หรือ /* select * from sys.indexes where object_id = (select object_id from sys.objects where name = 'PDK1_M_PAOUNDAM') And name is not null */-- หา Primary Key
exec sp_pkeys @tblName -- หรือ /* SELECT Col.Column_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name AND Tab.Constraint_Type = 'PRIMARY KEY' AND Col.Table_Name = 'PDK1_M_PAOUNDAM' */-- หา Size Table
EXEC sp_spaceused @tblName-- หา Dependency
SELECT name,type,type_desc,referenced_server_name,referenced_database_name,referenced_schema_name,referenced_entity_name FROM sys.sql_expression_dependencies A, sys.objects B WHERE referenced_id = OBJECT_ID(@tblName) AND A.referencing_id = B.object_id order by name --หรือ /* select B.name ,B.type, B.type_desc, A.referenced_server_name, A.referenced_database_name ,A.referenced_schema_name ,A.referenced_entity_name from sys.sql_expression_dependencies A inner join sys.objects B ON A.referencing_id = B.object_id Where A.referenced_entity_name = 'PDK1_M_PAOUNDAM' order by B.name */
Post by Goborijung at 2023-12-01 14:30:43 | ID: 1993
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
Post by Goborijung at 2022-07-27 11:11:26 | ID: 1727
--Insert Into TM_PO(PONo,PODate,POType,VendCode,VendName,ItemCode,Description,Unit,UnitCost,CostAmount,ExchangeRate,POQty,Branch,Department,RecQty,AdditionalQty,NAVLineNo) --SELECT a.No_ --,a.[Order Date] --,'0' as POType --,a.[Buy-FROM Vendor No_] --,a.[Pay-to Name] --,b.No_ as ItemCode --,b.[Description] --,b.[Unit of Measure] --,cast(round(b.[Unit Cost],4) as decimal(18,4)) as [Unit Cost] --,cast(round(b.[Line Amount],2) as decimal(18,2)) as [Line Amount] --,cast(round(a.[Currency Factor],2) as decimal(18,2)) as [Currency Factor] --,cast(round(b.Quantity,2) as decimal(18,2)) as Quantity --,1 as Branch --,9 as Department --,'0' AS RecQty --,'0' as AdditQty --,b.[Line No_] --FROM [192.168.1.193].ThaiParfun_golive.dbo.[Thai Parfun Co_,Ltd$Purchase Header] a inner join [192.168.1.193].ThaiParfun_golive.dbo.[Thai Parfun Co_,Ltd$Purchase Line] b On a.No_ = b.[Document No_] --WHERE Type = 2 AND b.No_ <> '' And [Document No_] in('PSO21120194') --And b.No_ in('PKDT-M04215','PKDT-M04216','PKDT-M04217','PKDT-M04218','PKDT-M04219','PKDT-C86140','PKDT-C86141','PKDT-C86142','PKDT-C86143','PKDT-C86144','PKDT-C86147','PKDT-C86148','PKDT-C86149','PKDT-C86150','PKDT-C86151')
Post by Goborijung at 2021-04-22 16:10:59 | ID: 1137
ตัวอย่าง
Select cast(round(150.444444444444,4) as decimal(18,4)) as cutDigit -- 150.4444 หรือ Select CAST('150.4444444' AS DECIMAL(15, 2)) --150.44
Post by Goborijung at 2020-07-08 11:07:22 | ID: 660
Tools > Option... > Designers > เอาเครื่องหมายถูกหน้า Prevent saving changes that require ... ออก