Technology — Page 2

T-SQL – Search Stored Procedure Code for Text

February 6, 2013 by Carlos Ferreira 3 Comments

Often I have to search for stored procedures in SQL Server to find pieces of text. The motivation for this is to find procedures which use a table or to look for code which the developer has done silly things like hardcoded directories (Boris….). Luckly you can query sys.procedures to generally find the information that you need. Below is an example of query that searches for stored procedures for the word ‘c:\*’.

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%c:\*%'

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%c:\*%'

REFERENCES:

Technet – sys.procedures

Filed Under: SQL Server, Technology Tagged With: keyword, Search, SQL Server, Stored Procedure, T-SQL, Table, tables, Text

C# Methods with Unlimited Parameters

January 11, 2013 by Carlos Ferreira 2 Comments

To create a method that can take unlimited parameters, utilize the params keyword in the method declaration. This allows you to specify a method parameter that take an argument where the number of arguments is variable. You are not allowed to have any other method parameters declared after a params method parameter.

MSDN resource: params

Example:

 static void Main(string[] args)
        {
            AddNumbers(12, 14, 15);
        }

 public static void AddNumbers(params int[] numbers)
        {
            int result = 0;
            for (int i = 0; i &lt; numbers.Length; i++)
                result+= numbers[i];
            Console.WriteLine(result);
        }

static void Main(string[] args)

{

AddNumbers(12, 14, 15);

}

public static void AddNumbers(params int[] numbers)

{

int result = 0;

for (int i = 0; i < numbers.Length; i++)

result+= numbers[i];

Console.WriteLine(result);

}

PSGMUMGGBJAR

Filed Under: C#, Technology, Uncategorized Tagged With: .Net, C Sharp, C#, Methods, parameters, unlimited

How to determine the version and edition of SQL Server

January 9, 2013 by Carlos Ferreira Leave a Comment

Connect to the instance of SQL Server, and then run the following query (this will work for all versions of SQL Server):

Select @@version

1	Select @@version

The result form the query will look like the following.

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

For version of SQL Server 2005 and newer you also can use the SERVERPROPERTY function to retrieve additional information. Executing the following will provide you with the SQL Server product version (i.e. 10.0.2531.0, product level (RTM) and edition information (i.e. Standard, Enterprise):

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

1	SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

You can lookup the product version using the following table:

Release	Product Version
SQL Server 2012 Service Pack 1	11.00.3000.00
SQL Server 2012 RTM	11.00.2100.60
SQL Server 2008 R2 Service Pack 1	10.50.2500.0
SQL Server 2008 R2 RTM	10.50.1600.1
SQL Server 2008 Service Pack 3	10.00.5500.00
SQL Server 2008 Service Pack 2	10.00.4000.00
SQL Server 2008 Service Pack 1	10.00.2531.00
SQL Server 2008 RTM	10.00.1600.22
SQL Server 2005 Service Pack 4	9.00.5000.00
SQL Server 2005 Service Pack 3	9.00.4035
SQL Server 2005 Service Pack 2	9.00.3042
SQL Server 2005 Service Pack 1	9.00.2047
SQL Server 2005 RTM	9.00.1399
SQL Server 2000 Service Pack 4	8.00.2039
SQL Server 2000 Service Pack 3	8.00.760
SQL Server 2000 Service Pack 2	8.00.534
SQL Server 2000 Service Pack 1	8.00.384
SQL Server 2000 RTM	8.00.194

Where to find information about the latest SQL Server builds use the following link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;957826

Filed Under: SQL Server, Technology, Uncategorized Tagged With: @@VERSION, edition, productlevel, productversion, SQL Server, T-SQL, TSQL

How to get the column names of a table in SQL Server (T-SQL)

January 8, 2013 by Carlos Ferreira 4 Comments

The following query can be run (substitute the ‘TableName’ in the query with the table name you want column information on) to get the column names for any table in T-SQL.

select column_name from information_schema.columns
where table_name = 'TableName'
order by ordinal_position

select column_name from information_schema.columns

where table_name = 'TableName'

order by ordinal_position

To get information on all the columns for all tables in a database use the following query:

SELECT Table_Schema, Table_Name, Column_Name, Data_Type
FROM information_schema.columns

1 2	SELECT Table_Schema, Table_Name, Column_Name, Data_Type FROM information_schema.columns

SQL Server also provides a stored procedure (‘sp_help’) which is able to display information on any object listed in sysobjects. To use this to get information on a table execute the following:

EXEC sp_help 'tablename'

1	EXEC sp_help 'tablename'

Filed Under: SQL Server, Technology Tagged With: column, name, SQL Server, T-SQL, Table

Enable xp_cmdshell using sp_configure

August 1, 2012 by Carlos Ferreira Leave a Comment

When you install a SQL Server instance, any feature that is not necessary for the core engine to run has been disabled by default. xp_cmdshell is a significant security risk because it allows a compromised SQL Server to elevate the attack to the operating system itself, and from there to the entire network.

You can enable or disable features within your instance by executing the system stored procedure sp_configure with the xp_cmdshell option.

When the xp_cmdshell feature is disabled you will see the following message when it is executed:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

You can verify that the xp_cmdshell feature featured is disabled by executing the following query:

SELECT value_in_use
FROM sys.configurations
WHERE name = 'xp_cmdshell'

SELECT value_in_use

FROM sys.configurations

WHERE name = 'xp_cmdshell'

If the results of the query is 0 then the feature is disabled; if 1 then it is enabled.

In order to enable xp_cmdshell execute the following:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

-- To update the currently configured value for advanced options.

RECONFIGURE

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

-- To update the currently configured value for this feature.

RECONFIGURE

Filed Under: SQL Server, Technology Tagged With: access, blocked, enable, Microsoft, security, sp_configure, SQL Server, xp_cmdshell

T-SQL – Search Stored Procedure Code for Text

Like this:

C# Methods with Unlimited Parameters

Like this:

How to determine the version and edition of SQL Server

Like this:

How to get the column names of a table in SQL Server (T-SQL)

Like this:

Enable xp_cmdshell using sp_configure

Like this:

Carlos Ferreira

Advertisement

Articles

Archives

T-SQL – Search Stored Procedure Code for Text

Share this:

Like this:

C# Methods with Unlimited Parameters

Share this:

Like this:

How to determine the version and edition of SQL Server

Share this:

Like this:

How to get the column names of a table in SQL Server (T-SQL)

Share this:

Like this:

Enable xp_cmdshell using sp_configure

Share this:

Like this:

Carlos Ferreira

Advertisement

Articles

Archives