{"id":839,"date":"2018-01-28T14:27:50","date_gmt":"2018-01-28T23:27:50","guid":{"rendered":"\/blog\/?p=839"},"modified":"2023-09-21T09:37:38","modified_gmt":"2023-09-21T00:37:38","slug":"transact-sqlms-sql-%ea%b8%b0%eb%b3%b8-%eb%ac%b8%eb%b2%95","status":"publish","type":"post","link":"https:\/\/hasu0707.duckdns.org\/blog\/?p=839","title":{"rendered":"Transact SQL(MS SQL) \uae30\ubcf8 \ubb38\ubc95"},"content":{"rendered":"\n
\u25a0 \uc815\ubcf4<\/span><\/font><\/div>
_test \u2192 login \ubc0f user\uba85 suffix<\/span><\/font><\/div>
db_mydb \u2192 DB\uba85<\/span><\/font><\/div>
<mypassword> \u2192 \ube44\ubc00\ubc88\ud638<\/span><\/font><\/div>

<\/span><\/font><\/div>

<\/span><\/font><\/div>
\u25a0 jdbc \uc5f0\uacb0<\/span><\/font><\/div>
jdbc:sqlserver:\/\/<host>:1433;database=mydb;connectionCollation=korean_wansung_cs_as;sendStringParametersAsUnicode=false<\/span><\/font><\/div>

<\/span><\/font><\/div>
\u203b SQL Server \uc758 \uae30\ubcf8 Collation\uc740 Korean_Wansung_CI_AS \ub85c \uad6c\uc131\ub418\uc5b4 \uc788\ub2e4.<\/span><\/font><\/div>
Korean \/ \ud55c\uae00<\/span><\/font><\/div>
Wansung \/ \uc644\uc131\ud615<\/span><\/font><\/div>
CI \/ Case Insensitive (\ub300\uc18c\ubb38\uc790 \uad6c\ubd84\ud558\uc9c0 \uc54a\uc74c)<\/span><\/font><\/div>
CS \/ Case Sensitive (\ub300\uc18c\ubb38\uc790 \uad6c\ubd84)<\/span><\/font><\/div>
AS \/ Accent Insensitive (\uc545\uc13c\ud2b8 \uad6c\ubd84\ud558\uc9c0 \uc54a\uc74c)<\/span><\/font><\/div>

<\/span><\/font><\/div>

<\/span><\/font><\/div>
\u25a0 SQL<\/span><\/font><\/div>

<\/span><\/font><\/div>
--\ucd08\uae30\ud654<\/span><\/font><\/div>
DROP USER \"usr_test\";<\/span><\/font><\/div>
DROP LOGIN \"login_test\";<\/span><\/font><\/div>
DROP DATABASE \"db_mydb\";<\/span><\/font><\/div>

<\/span><\/font><\/div>

<\/span><\/font><\/div>
--\ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc0dd\uc131<\/span><\/font><\/div>
CREATE DATABASE \"db_mydb\" COLLATE korean_wansung_cs_as;<\/span><\/font><\/div>

<\/span><\/font><\/div>

<\/span><\/font><\/div>
--\uc0ac\uc6a9\uc790 \uc0dd\uc131<\/span><\/font><\/div>
USE \"db_mydb\";<\/span><\/font><\/div>
CREATE LOGIN \"login_test\" WITH PASSWORD='<mypassword>';<\/span><\/font><\/div>
CREATE USER \"usr_test\" FOR LOGIN \"login_test\";<\/span><\/font><\/div>
GRANT EXECUTE TO \"usr_test\";<\/span><\/font><\/div>
ALTER LOGIN \"login_test\" WITH DEFAULT_DATABASE = \"db_mydb\";<\/span><\/font><\/div>
EXEC SP_ADDROLEMEMBER 'db_owner', 'usr_test';<\/span><\/font><\/div>

<\/span><\/font><\/div>

<\/span><\/font><\/div>
--DB\uc758 \ubaa8\ub4e0 \uc81c\uc57d\uc870\uac74 \ud574\uc81c1<\/span><\/font><\/div>
USE \"db_mydb\";<\/span><\/font><\/div>
EXEC sp_MSforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"<\/span><\/font><\/div>

<\/span><\/font><\/div>

<\/span><\/font><\/div>
--\ub300\uc18c\ubb38\uc790\ub97c \uad6c\ubd84\ud558\ub294 \ube44\uc5b4 \uc788\uc9c0 \uc54a\uc740 \ud45c\ub97c \ucc3e\ub294\ub2e4.<\/span><\/font><\/div>
USE \"db_mydb\";<\/span><\/font><\/div>
EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '<\/span><\/font><\/div>

<\/span><\/font><\/div>

<\/span><\/font><\/div>
--DB\uc758 \ubaa8\ub4e0 \uc81c\uc57d\uc870\uac74 \ud574\uc81c2<\/span><\/font><\/div>
USE \"db_mydb\";<\/span><\/font><\/div>
EXEC sp_MSforeachtable \"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all\"<\/span><\/font><\/div>

<\/span><\/font><\/div>

<\/span><\/font><\/div>
--DB \uc778\ub371\uc2a4 \uc7ac\uc0dd\uc131<\/span><\/font><\/div>
USE \"db_mydb\";<\/span><\/font><\/div>
DECLARE @TableName VARCHAR(255)<\/span><\/font><\/div>
DECLARE @sql NVARCHAR(500)<\/span><\/font><\/div>
DECLARE @fillfactor INT<\/span><\/font><\/div>
SET @fillfactor = 80<\/span><\/font><\/div>
DECLARE TableCursor CURSOR FOR<\/span><\/font><\/div>
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName<\/span><\/font><\/div>
FROM sys.tables<\/span><\/font><\/div>
OPEN TableCursor<\/span><\/font><\/div>
FETCH NEXT FROM TableCursor INTO @TableName<\/span><\/font><\/div>
WHILE @@FETCH_STATUS = 0BEGIN<\/span><\/font><\/div>
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH<\/span><\/font><\/div>
(FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'<\/span><\/font><\/div>
EXEC (@sql)<\/span><\/font><\/div>
FETCH NEXT FROM TableCursor INTO @TableName<\/span><\/font><\/div>
END<\/span><\/font><\/div>
CLOSE TableCursor<\/span><\/font><\/div>
DEALLOCATE TableCursor<\/span><\/font><\/div>
GO<\/span><\/font><\/div><\/div><\/div><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

\u25a0 \uc815\ubcf4 _test \u2192 login \ubc0f user\uba85 suffix db_mydb \u2192 DB\uba85 <mypassword> \u2192 \ube44\ubc00\ubc88\ud638 \u25a0 jdbc \uc5f0\uacb0 jdbc:sqlserver:\/\/<host>:1433;database=mydb;connectionCollation=korean_wansung_cs_as;sendStringParametersAsUnicode=false \u203b SQL Server \uc758 \uae30\ubcf8 Collation\uc740 Korean_Wansung_CI_AS \ub85c \uad6c\uc131\ub418\uc5b4 \uc788\ub2e4. Korean \/ \ud55c\uae00 Wansung \/ \uc644\uc131\ud615 CI \/ Case Insensitive (\ub300\uc18c\ubb38\uc790 \uad6c\ubd84\ud558\uc9c0 \uc54a\uc74c) CS \/ Case Sensitive (\ub300\uc18c\ubb38\uc790 \uad6c\ubd84) AS \/ Accent Insensitive (\uc545\uc13c\ud2b8 \uad6c\ubd84\ud558\uc9c0 \uc54a\uc74c) \u25a0 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[10],"tags":[],"class_list":["post-839","post","type-post","status-publish","format-standard","hentry","category-computing_database"],"_links":{"self":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/839","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=839"}],"version-history":[{"count":0,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/839\/revisions"}],"wp:attachment":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=839"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=839"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=839"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}