Tag: SQL server

I would like to find all the stored procedures that has reference of Functions or called Functions from Store procedures.

Here is the script;

Select 
	Schema_Name(p.schema_id) As ProcedureSchema, p.name As ProcedureName, 
	Schema_Name(f.schema_id) As FunctionSchema, f.name as FunctionName
From sys.sql_expression_dependencies d
Inner Join sys.objects p On 
	p.object_id = d.referencing_id And p.type_desc = 'SQL_STORED_PROCEDURE'
Inner Join sys.objects f On 
	f.object_id = d.referenced_id And f.type In ('AF', 'FN', 'FS', 'IF', 'TF');

How to restore a higher version SQL Server database backup file onto a lower version SQL Server?

We can use functionality called Export Data-Tier Application which generates .bacpac file consisting database schema and data.

https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/export-a-data-tier-application?view=sql-server-ver16

On destination server, you can use Import Data-Tier Application option which creates and populates new database from pre-created .bacpac file

https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/import-a-bacpac-file-to-create-a-new-user-database?view=sql-server-ver16

If you want just to transfer database schema, you can use Extract Data-Tier Application for creating file and Deploy Data-Tier Application for deploying created database schema.

I’ve tried this process on different versions of SQL Server from SQL 2022 to SQL 2017, SQL 2014 to SQL 2012 and from SQL 2014 to SQL 2008R2 and worked well.

Here is how;

SELECT  
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address 

Reference

https://stackoverflow.com/questions/142142/sql-query-to-get-servers-ip-address#:~:text=To%20do%20this%2C%20create%20a,ll%20get%20the%20IP%20address.

This is a tip to drop/create temp tables in Stored Procedures;

IF OBJECT_ID('tempdb..#add') IS NOT NULL DROP TABLE #add
SELECT 
   ...
INTO #add
FROM MyTable

The script below will list all the primary keys, that have at least one int or bigint in their columns with all other ask. 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 


SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , p.partition_number
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
    ,K.increment_value as IncrementValue
    ,K.last_value as LastValue
    ,K.seed_value as SeedValue
    ,k.is_nullable
    ,k.is_identity
    ,k.is_filestream
    ,k.is_replicated
    ,k.is_not_for_replication
FROM sys.partitions p

INNER JOIN sys.indexes i 
        ON p.object_id = i.object_id
       AND p.index_id = i.index_id


INNER JOIN SYS.TABLES S 
         ON S.object_id = P.object_id

LEFT OUTER JOIN sys.identity_columns K
             ON P.object_id = K.object_id

WHERE 1=1

  AND EXISTS ( SELECT 1 
                    FROM SYS.COLUMNS C
              INNER JOIN sys.types AS t 
                         ON c.user_type_id=t.user_type_id
                   WHERE i.object_id = c.object_id
                   AND T.user_type_id IN (127,56)  -- ONLY BIGINT AND INT
             )

  AND I.is_primary_key = 1

  -- AND i.index_id < 2  -- GET ONLY THE CLUSTERED INDEXES - IF EXISTS ANY
                      -- get heaps too

  --AND k.is_identity = 1 -- GET ONLY THE IDENTITY COLUMNS


ORDER BY [Schema], [Table], [Index]

Reference

https://dba.stackexchange.com/questions/165266/search-all-table-find-primarykeys-with-id-int-bigint-and-enable-identity-aut