'SP'에 해당되는 글 2건

  1. 2008.11.13 카탈로그내에서 특정 컬럼을 참조하는 SP조회
  2. 2008.08.20 [MSSQL]테이블 소유자 변경하기

카탈로그내에서 특정 컬럼을 참조하는 SP조회

DATABASE/SQLServer 2008. 11. 13. 18:37





obj2.name as SP_Name,

obj2.id as SP_ID

from sysdepends dep, sysobjects obj, sysobjects obj2, information_schema.columns col

where dep.depid = obj.id

  and dep.id    = obj2.id

  and obj.name  = col.table_name

  and col.column_name = 'ssn'

  and obj2.xtype='P'

group by col.table_catalog, col.table_name, col.column_name, obj2.name, obj2.id

-- 테이블 리스트
select * from information_schema.tables
-- 컬럼 상세 정보
select * from information_schema.columns
where table_name = '테이블명'
order by 5

tags : msSQL, SP, 컬럼
Trackbacks 0 : Comments 0

Write a comment

[MSSQL]테이블 소유자 변경하기

DATABASE/SQLServer 2008. 8. 20. 11:13


sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'


[ @objname = ] 'object'

Is the name of an existing table, view, user-defined function, or stored procedure in the current database. object is an nvarchar(776), with no default. object can be qualified with the owner of the existing object, in the form existing_owner.object if the schema and its owner have the same name.

[ @newowner=] 'owner '

Is the name of the security account that will be the new owner of the object. owner is sysname, with no default. owner must be a valid database user, server role, Microsoft Windows login, or Windows group with access to the current database. If the new owner is a Windows user or Windows group for which there is no corresponding database-level principal, a database user will be created.

Return Code Values

0 (success) or 1 (failure)


sp_changeobjectowner removes all existing permissions from the object. You will have to reapply any permissions that you want to keep after running sp_changeobjectowner. Therefore, we recommend that you script out existing permissions before running sp_changeobjectowner. After ownership of the object has been changed, you can use the script to reapply permissions. You must modify the object owner in the permissions script before running. For more information about database scripting, see Documenting and Scripting Databases.

To change the owner of a securable, use ALTER AUTHORIZATION. To change a schema, use ALTER SCHEMA.


Requires membership in the db_owner fixed database role, or membership in both the db_ddladmin fixed database role and the db_securityadmin fixed database role, and also CONTROL permission on the object.


The following example changes the owner of the authors table to Corporate\GeorgeW.

EXEC sp_changeobjectowner 'authors', 'Corporate\GeorgeW';

'DATABASE > SQLServer' 카테고리의 다른 글

[MSSQL]남은 디스크공간 알아내는 방법  (0) 2008.11.21
카탈로그내에서 특정 컬럼을 참조하는 SP조회  (0) 2008.11.13
[MSSQL]테이블 소유자 변경하기  (0) 2008.08.20
MSSQL 괜찮은 명령어들  (0) 2008.07.30
Trigger  (0) 2008.07.30
SET IDENTITY_INSERT  (0) 2008.07.09
tags : msSQL, SP, 소유자
Trackbacks 0 : Comments 0

Write a comment