Difference between revisions of "MSSQL"

From WhyAskWhy.org Wiki
Jump to: navigation, search
(Stub page.)
 
m (Added an example of using stored procedure)
 
Line 2: Line 2:
 
[[Category:Software]]
 
[[Category:Software]]
 
[[Category:Database]]
 
[[Category:Database]]
 
'''Note: Examples shown here were tested on Microsoft SQL Server 2008.'''
 
  
 
= Microsoft SQL Server 2008 Tips/Tricks =
 
= Microsoft SQL Server 2008 Tips/Tricks =
  
== Overview ==
+
For all examples shown here, the database if not explicitly shown is named <code>Services</code>. Snippets were tested on Microsoft SQL Server 2008.
  
For all examples shown here, the database if not explicitly shown is named <code>Services</code>.
 
  
 
== Show all columns of a database table ==
 
== Show all columns of a database table ==
  
<ref name="mssql-show-columns" />
+
<ref name="mssql-show-columns-1" /> <ref name="mssql-show-columns-2" />
  
Examples (equivalent results):
+
Examples (''mostly'' equivalent results):
  
 
* <syntaxhighlight lang="tsql">SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users')</syntaxhighlight>
 
* <syntaxhighlight lang="tsql">SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users')</syntaxhighlight>
 
* <syntaxhighlight lang="tsql">SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Services.dbo.Users'</syntaxhighlight>
 
* <syntaxhighlight lang="tsql">SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Services.dbo.Users'</syntaxhighlight>
 +
* <syntaxhighlight lang="tsql">exec sp_help 'Users'</syntaxhighlight>
  
 
In my case I had drilled down using the <code>Microsoft SQL Server Management Studio</code> prior to running the queries, so the tables could have simply been specified as <code>Users</code> with the same result.
 
In my case I had drilled down using the <code>Microsoft SQL Server Management Studio</code> prior to running the queries, so the tables could have simply been specified as <code>Users</code> with the same result.
 +
  
 
== References ==
 
== References ==
Line 26: Line 25:
 
<references>
 
<references>
  
<ref name="mssql-show-columns">[http://stackoverflow.com/questions/1054984/get-columns-of-a-table-sql-server Get columns of a table SQL SERVER]</ref>
+
<ref name="mssql-show-columns-1">[http://stackoverflow.com/questions/1054984/get-columns-of-a-table-sql-server Get columns of a table SQL SERVER]</ref>
 +
<ref name="mssql-show-columns-2">[http://stackoverflow.com/questions/100504/what-is-the-sql-command-to-return-the-field-names-of-a-table What is the SQL command to return the field names of a table?]</ref>
  
 
</references>
 
</references>

Latest revision as of 17:27, 10 December 2014


Microsoft SQL Server 2008 Tips/Tricks

For all examples shown here, the database if not explicitly shown is named Services. Snippets were tested on Microsoft SQL Server 2008.


Show all columns of a database table

[1] [2]

Examples (mostly equivalent results):

  • SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users')
    
  • SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Services.dbo.Users'
    
  • exec sp_help 'Users'
    

In my case I had drilled down using the Microsoft SQL Server Management Studio prior to running the queries, so the tables could have simply been specified as Users with the same result.


References