Sql Server Materials

How to  verify the CDC is enabled data bases or tables The following query  will get list  of  CDC enabled data bases  SELECT     name AS DatabaseName,     is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1; The following query  will get list  of  CDC enabled  tables  with in the  database.  USE <DB Name>;  -- Replace with actual DB name GO SELECT      s.name AS SchemaName,     t.name AS TableName,     c.capture_instance,     c.supports_net_changes,     c.start_lsn,     c.end_lsn FROM      cdc.change_tables c JOIN      sys.tables t ON c.source_object_id = t.object_id JOIN      sys.schemas s ON t.schema_id = s.schema_id; Below will get the list of tables which  are enabled CDC from all data bases.  DECLARE @dbName NVARCHAR(128) DECLARE @sql NVARCHAR(MAX) -- Cursor to loop ...

  Below Query  help  bring the  server  login name , data base level login name  along with their roles/members.  This query will help us to validate the roles/ members are mapped correctly or not.  USE master  GO CREATE TABLE #temp ( id INTEGER  IDENTITY(1,1), DatabasseName VARCHAR(100), login_name VARCHAR(500), database_user VARCHAR(500), role_name VARCHAR(500), user_type VARCHAR(500), authentication_type_desc VARCHAR(500) ) EXEC sp_foreachdb  ' USE ? INSERT INTO #temp (DatabasseName,login_name,database_user,role_name,user_type,authentication_type_desc) SELECT ''?'' AS DatabasseName,     sp.name AS login_name,     dp.name AS database_user,     dr.name AS role_name,     dp.type_desc AS user_type,     dp.authentication_type_desc FROM      sys.server_principals sp  JOIN       sys.database_principals dp ON sp.sid = dp.si...

 Hello,  To determine the filegroup where an index is created in SQL Server, you can use the following methods: 1. Using  sys.indexes  and  sys.filegroups  System Views You can query the system views to find the filegroup of an index: Sql Copy code SELECT i.name AS IndexName, o.name AS TableName, f.name AS FileGroupName FROM sys.indexes i JOIN sys.objects o ON i.object_id = o.object_id JOIN sys.data_spaces f ON i.data_space_id = f.data_space_id WHERE o.type = 'U' ; -- Only user tables This query will list all indexes, their associated tables, and the filegroups they belong to. 2. Using SQL Server Management Studio (SSMS) Open SSMS and connect to your database. Navigate to the table where the index is created. Expand the  Indexes  folder under the table. Right-click the index and select  Properties . In the  Storage  section, you can see the filegroup where the index is stored. 3. Using...