Tsql ile bir serverdan diğerine extended property kopyalama

Copy Extended property from one SQL Server To another by tsql:

SELECT 

'EXEC sys.sp_addextendedproperty @name=N''MS_Description'', 

@value=N'''+replace(

CAST(exprop.value AS VARCHAR(max))

,'''','"')

+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''

+tbl.name+''', @level2type=N''COLUMN'',@level2name=N'''+clmns.name +''';'

,tbl.name,

CAST(clmns.name AS VARCHAR(35)) ,exprop.value 

FROM sys.tables AS tbl

INNER JOIN sys.all_columns AS clmns

ON clmns.object_id=tbl.object_id

LEFT OUTER JOIN sys.indexes AS idx

ON idx.object_id = clmns.object_id

AND 1 =idx.is_primary_key

LEFT OUTER JOIN sys.index_columns AS idxcol

ON idxcol.index_id = idx.index_id

AND idxcol.column_id = clmns.column_id

AND idxcol.object_id = clmns.object_id

AND 0 = idxcol.is_included_column

LEFT OUTER JOIN sys.types AS udt

ON udt.user_type_id = clmns.user_type_id

LEFT OUTER JOIN sys.types AS typ

ON typ.user_type_id = clmns.system_type_id

AND typ.user_type_id = typ.system_type_id

LEFT JOIN sys.default_constraints AS cnstr

ON cnstr.object_id=clmns.default_object_id

LEFT OUTER JOIN sys.extended_properties exprop

ON exprop.major_id = clmns.object_id

AND exprop.minor_id = clmns.column_id

AND exprop.name = 'MS_Description'

WHERE (--tbl.name = @TableName and 

exprop.class = 1) --I don't wand to include comments on indexes

ORDER BY clmns.column_id ASC

Moğolca-Türkçe çeviri - http://turkcemogolcatercume.weebly.com/

Türkçe Moğolca çeviriler için Lütfen mail atın:


mahmuthaktan@gmail.com


şağıdaki sitede Moğolca Türkçe çevirileriniz profesyonel olarak yapılıyor.  Moğolca alfabeyide siteden inceleyebilirsiniz.

How to get SQL Server schema changes especially “column name” changes

up vote0down votefavorite 1

I am using trigger to trace DDL changes but when I change column name from the SQL Server Management Studio the trigger is not working? create TRIGGER trgLogDDLEvent ON DATABASE   FOR DDL_DATABASE_LEVEL_EVENTSFOR AS DECLARE @data XMLSET @data = EVENTDATA() IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_STATISTICS' INSERT INTO db_trc_DDLChangeLog( EventType, ObjectName, ObjectType, tsql, RecDate, HostName) VALUES ( @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), getdate(),  HOST_NAME() ) ; GO --select * from db_trc_DDLChangeLog --create table db_trc_DDLChangeLog --( --EventType varchar(max), --ObjectName varchar(max), --ObjectType varchar(max), --tsql varchar(max), --RecDate datetime, --HostName varchar(max) --) sql sql-server sql-server-2005 tsql link|edit|delete|flag edited Oct 22 at 13:21 asked Oct 22 at 12:54 MahmutHAKTAN 325 75% accept rate

If you are changing database structure from the Object Explorer and not a script, then stop that right now. No database change should ever be made without a script that is in source control. – HLGEM Oct 22 at 13:17 I just want to trace this changes for security. – MahmutHAKTAN Oct 22 at 13:28  add comment

1 Answer

activenewestvotes

up vote0down voteaccept

You won't get events specifically for column changes. Instead you'll receive an ALTER_TABLE event to indicate the table has changed. When you say SQL Server console do you mean Management Studio? If so, have a look at the SQL it is generating to alter your table. It might be creating a new table, copying the data to it, dropping the old table and renaming the new table so you will see a number of events like CREATE_TABLE and thenDROP_TABLE. link|flag edited Oct 22 at 14:00 answered Oct 22 at 13:16 Andrew Kennan 2,788212

Yes I mean Management studio. trigger is running for all DLL events is't it?: FOR DDL_DATABASE_LEVEL_EVENTS. I also tried CREATE_TABLE, ALTER_TABLE, DROP_TABLE, events and is was not worked. – MahmutHAKTAN Oct 22 at 13:22    up vote   flag Is your trigger being called at all? Could there be some other problem preventing a row being inserted into db_trc_DDLChangeLog? Also, if the SQL generated by management studio is using sp_rename it won't cause a DDL event to be raised in SQL Server 2005. – Andrew Kennan Oct 22 at 13:59 no problem for db_trc_DDLChangeLog because when i drop table it writes to log ı think sp_rename not firing DDL event.. – MahmutHAKTAN Oct 25 at 13:01  Yeah - sp_rename was fixed for SQL 2008 but in 2005 it won't raise an event. – Andrew Kennan Oct 26 at