Query to List all the partitioned tables in SQL Server database

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object],
    i.name as [index],
    s.name as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id order by [schema]

Script to Load SQL Profiler trace file data into a table in SQL Server

SELECT *
FROM fn_get_audit_file('E:\Microsoft SQL Server\SQL Server Auditing\Audit-20120828-143913_CDE79597-4A8E-4C51-ACBB-35C0F8278C85_0_129906638124380000.sqlaudit', DEFAULT, DEFAULT)

Script to list data types of all columns in a Database in SQL Server

SQL Server tables displays the data type of
each column inside the table. You can do this dozens of ways, but a popular method shown in
the following example joins the sys.objects table with the sys.columns table. There are two
functions that you may not be familiar with in the following code. The TYPE_NAME() function
translates the data type id into its proper name. To go the opposite direction, you could use the
TYPE_ID() function. The other function of note is SCHEMA_ID(), which is used to return the
identity value for the schema. This is useful primarily when you want to write reports against the
SQL Server metadata.

USE AdventureWorks
GO

SELECT o.NAME AS ObjectName
 ,c.NAME AS ColumnName
 ,TYPE_NAME(c.user_type_id) AS DataType
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.NAME = 'Department'
 AND o.Schema_ID = SCHEMA_ID('HumanResources')

Script to automatically create insert statements for table data in SQL Server

CREATE procedure  [dbo].[INS]                             

(                                                         

   @Query  Varchar(MAX)                                                         

)                             

AS                            

   Set nocount ON                 

DEclare @WithStrINdex as INT                           

DEclare @WhereStrINdex as INT                           

DEclare @INDExtouse as INT                            

Declare @SchemaAndTAble VArchar(270)                           

Declare @Schema_name  varchar(30)                           

Declare @Table_name  varchar(240)                           

declare @Condition  Varchar(MAX)                             

SET @WithStrINdex=0                           

SELECT @WithStrINdex=CHARINDEX('With',@Query )                           

, @WhereStrINdex=CHARINDEX('WHERE', @Query)                           

IF(@WithStrINdex!=0)                           

Select @INDExtouse=@WithStrINdex                           

ELSE                           

Select @INDExtouse=@WhereStrINdex                           

Select @SchemaAndTAble=Left (@Query,@INDExtouse-1)                                                     

select @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble))                           

Select @Schema_name= Left (@SchemaAndTAble, CharIndex('.',@SchemaAndTAble )-1)                           

,      @Table_name = SUBSTRING(  @SchemaAndTAble , CharIndex('.',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) )                           

,      @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6                           

Declare   @COLUMNS  table (Row_number SmallINT , Column_Name VArchar(Max) )                             

Declare @CONDITIONS as varchar(MAX)                             

Declare @Total_Rows as SmallINT                             

Declare @Counter as SmallINT             

declare @ComaCol as varchar(max)           

select @ComaCol=''                  

Set @Counter=1                             

set @CONDITIONS=''                             

INsert INTO @COLUMNS                             

Select  Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FRom INformation_schema.columns Where Table_schema=@Schema_name                             

And table_name=@Table_name        

and Column_Name not in ('SyncDestination','PendingSyncDestination' ,'SkuID','SaleCreditedto')                  

select @Total_Rows= Count(1) FRom  @COLUMNS                             

             Select @Table_name= '['+@Table_name+']'                     

             Select @Schema_name='['+@Schema_name+']'                     

While (@Counter<=@Total_Rows )                             

begin                              

--PRINT @Counter                             

    select @ComaCol= @ComaCol+'['+Column_Name+'],'           

    FROM @COLUMNS                             

Where [Row_number]=@Counter                         

select @CONDITIONS=@CONDITIONS+ ' +Case When ['+Column_Name+'] is null then ''Null'' Else ''''''''+                             

 Replace( Convert(varchar(Max),['+Column_Name+']  ) ,'''''''',''''  )                             

  +'''''''' end+'+''','''                             

FROM @COLUMNS                             

Where [Row_number]=@Counter                             

SET @Counter=@Counter+1                              

End                             

select @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2)                             

select @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)             

select @ComaCol= substring (@ComaCol,0,  len(@ComaCol) )                           

select @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS                             

select @CONDITIONS=@CONDITIONS+'+'+ ''')'''                             

Select @CONDITIONS= 'Select  '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition                             

print(@CONDITIONS)                             

Exec(@CONDITIONS) 

Exec [dbo].[INS]  'Person.PersonPhone where 1=1'

How do I create foreign Keyrelationship with a table in a different database?

You would need to manage the referential constraint across databases using a Trigger.

Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.

Example:

Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END

---

Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.

Credits:John Hartsock

T-SQL Script to load the trace file to a database table

T-SQL to load the trace file to a database table:

USE pubs
GO

SELECT *
INTO trace_table
FROM::fn_trace_gettable('c:\my_trace.trc', DEFAULT)