T-SQL magic

Posted on

How to get indexes of current database?

1
select * from sys.indexes

How to get replication articles?

1
2
3
4
5
6
7
8
9
10
11
SELECT 
  msp.publication AS PublicationName,
  msa.publisher_db AS DatabaseName,
  msa.article AS ArticleName,
  msa.source_owner AS SchemaName,
  msa.source_object AS TableName
FROM distribution.dbo.MSarticles msa
JOIN distribution.dbo.MSpublications msp ON msa.publication_id = msp.publication_id
ORDER BY 
  msp.publication, 
  msa.article
1
2
select * from syspublications
select * from sysarticles where pubid = 3

Useful replication stored procedure.

1
2
3
4
5
6
7
8
9
exec sp_dropsubscription
    @publication='**-Publication',
    @article= N'all',
    @subscriber = 'WIN-VPE**FSH8IJ'

exec sp_droppublication
    @publication='**-Publication'

exec sp_removedbreplication @dbname='**-local'

How to close connect and drop db in sql server?

1
2
alter database [dbname] set SINGLE_USER with rollback immediate
drop database [dbname]

Other collections